Error occurred while retrieving the data.

Question

This is my expression rule:-

a!queryEntity(
entity: cons!SSB_IAL_EMP_DETAILS_VIEW,
query: a!query(
pagingInfo: a!pagingInfo(1, -1)
)
)

The error I'm getting-
Expression evaluation error at function a!queryEntity: An error occurred while retrieving the data.

I've attached a file with XSD and MySQL query for View definition. I have seen few earlier questions related the same issue and followed the solutions. Tried recreating everything again. But the challenge persists. Let me know the solution please.
In case you are wondering, yes the id column consists of unique values.

details.txt

Fredrick Omeniho · Accepted Answer

Hey All, 
 I am just very new in Appian, 2 weeks to be specific but I am not new to application database nor database development - 18 years experience as of today. I encountered this problem today with 3 of my tables and to be frank it is not meant to be. It's an Appian problem but it arises just because of only one reason, Appian was designed in JAVA. JAVA is object-oriented, so a lot of data validations have to be made for it to be purely synced with an RDBMS if a low code application design interface is desired. 
 After reading through these comments, I decided to make this issue is solved, being a database expert. 
 Simple and short: 
 Although I called it an Appian problem, the solution is getting your database design right and this fixes the problem. 
 1. Ensure every field of the type 'Date' or 'Date and Time', and all other 'Date' related types have a 'default value' specified. 
 2. Ensure your default value exactly matches your data type definition e.g. data type 'date and time' in MySQL can not have a default value of 'current timestamp', the data type must be defined as 'Timestamp' to have a default value of 'current Timestamp'. I did not test with MSSQL, but I guess it should be the same as it is because Appian isn't doing the minor auto conversion to avoid truncation I guess again. 
 3. In a case your design does not require a default value, ensure you specify the field as nullable if no value is provided when you are saving a record. 
 4. If you populated your table before starting your Appian design, then again you need to check your data as matching your table definition e.g. a field should not have a zero-length string, make it null; a date related field should not have a "0000-00-00 00:00:00" or a zero-length string, make it null. This is because Appian simulates your database content as an object because it was designed with JAVA. Appian is not designed to understand the zero-length string as a form of validation to reduce errors in Appian. Your RDBMS understands all these minor things but Appian was not designed that way. 
 soumyab243 pointed out that he changed a date type to decimal type but I believe that field did not have a default value that matches the field definition or did not allow null before he changed or the table was populated prior to creating the CDT, this why your problem went away. 
 Appian gets confused when your database design is not perfect, because of the fact that it literally sees or converts your database contents to objects. 
 It is 3 years now, this problem should have been fixed. I am fixing this because of people that might have this issue in the future. 
 Good luck and do not forget to upvote if it solves your problem.

Muhammad Awais · Answer

I was able to resolve this issue by using this. 
 In view don't use select * command , instead of these command you can take particular columns 
 from the table.

Error occurred while retrieving the data.

Top Replies