Unexpected error executing query (type: [SHANewListDT5369], query: [queryentity expression], order by: [[Sort[listingId desc]]], filters:[null])

Question

Hi all, 
 grid field is showing the error when I am query entity to view the data in the interface, the CDT are of the ARRAY type. when I am removing the filter I am getting this error if I keep the filter I am getting another error. can you please help me with this error. 
 a!localVariables( local!VerificationDecision, local!listingetails:a!queryEntity( entity: cons!SHA_LISTING_DS, query: a!query( selection: a!querySelection( columns: { a!queryColumn( field:"propertyTypeIdFk", alias:"propertyTypeIdFk" ), a!queryColumn( field:"propertyName", alias:"propertyName" ), a!queryColumn( field:"propertyAddress", alias:"propertyAddress" ), a!queryColumn( field:"propertyDescription", alias:"propertyDescription" ), a!queryColumn( field:"floorPlanName", alias:"floorPlanName" ), a!queryColumn( field:"neighborhood", alias:"neighborhood" ), a!queryColumn( field:"price", alias:"price" ), a!queryColumn( field:"squareFootage", alias:"squareFootage" ), a!queryColumn( field:"bedRooms", alias:"bedRooms" ), a!queryColumn( field:"bathRooms", alias:"bathRooms" ), a!queryColumn( field:"pets", alias:"pets" ), a!queryColumn( field:"doubleOccupancy", alias:"doubleOccupancy" ), } ), logicalExpression: a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: "listingId", operator: "in", value: ri!listing, applywhen: not(isnull(ri!listing)) )}, ignoreFiltersWithEmptyValues: true ), pagingInfo:a!pagingInfo( startIndex: 1, batchSize: -1, sort: a!sortInfo( field:"listingId", ascending: true() ) ), ), fetchTotalCount: true() ).data, 
 local!ammenityDetails:a!queryEntity( entity: cons!SHA_NEWAMMENITIES, query: a!query( selection: a!querySelection( columns: { a!queryColumn( field:"listingIdFk", alias:"listingIdFk" ), a!queryColumn( field:"ammenityTypeIdFk", alias:"ammenityTypeIdFk" ) } 
 ), logicalExpression: a!queryLogicalExpression( operator: "AND", filters: { a!queryFilter( field: "ammenityId", operator: "=", value: ri!ammenity, applywhen: not(isnull(ri!ammenity)) )}, ignoreFiltersWithEmptyValues: true ), pagingInfo:a!pagingInfo( startIndex: 1, batchSize: -1, sort: a!sortInfo( field:"ammenityId", ascending: true() ) ), 
 ), fetchTotalCount: true() ).data, 
 if( or(isnull(local!VerificationDecision), isnull(ri!listing) ), a!sectionLayout( label: "Student Status", contents: { a!gridField( label: "listingId", labelPosition: "ABOVE", data: a!queryEntity( entity: cons!SHA_LISTING_DS, query: a!query( /*filter: a!queryFilter(*/ /*field: "listingId",*/ /*operator: "=",*/ /*value: tointeger(index(local!listingetails.data,"listingId",null()))*/ /*),*/ pagingInfo: fv!pagingInfo ), fetchTotalCount: true ), columns: { a!gridColumn( label: "listingId", sortField: "listingId", value: a!linkField( links: { a!dynamicLink( label: fv!row.listingId, value: fv!row.listingId, saveInto: {local!VerificationDecision, a!save(ri!listing,a!queryEntity( entity: cons!SHA_LISTING_DS, query: a!query( filter: a!queryFilter( field: "listing", operator: "=", value: local!VerificationDecision, applyWhen: isnull(local!VerificationDecision)=false ), pagingInfo: a!pagingInfo( startIndex: 1, batchSize: -1 
 ) ) ).data 
 ), a!save(ri!button_txt,null 
 ) } ) } ), align: "END" ), a!gridColumn( label: "Property Name", sortField: "propertyName", value: a!linkField( links: { a!dynamicLink( label: fv!row.propertyName, value: fv!row.listing, saveInto: {local!VerificationDecision, a!save(ri!listing,a!queryEntity( entity: cons!SHA_LISTING_DS, query: a!query( filter: a!queryFilter( field: "listing", operator: "=", value: local!VerificationDecision, applyWhen: isnull(local!VerificationDecision)=false ), pagingInfo: a!pagingInfo( startIndex: 1, batchSize: -1 
 ) ) ).data 
 ), a!save(ri!button_txt,null 
 ) 
 } ) } ) ), a!gridColumn( label: "price", sortField: "price", value: fv!row.price ), a!gridColumn( label: "squareFootage", sortField: "squareFootage", value: fv!row.squareFootage ), 
 }, pageSize: 10, initialSorts: { a!sortInfo( field: "listingId", ascending: false ) }, pagingSaveInto: local!listingetails, validations: {}, spacing: "DENSE", height: "AUTO", borderStyle: "STANDARD", refreshAlways: true ) }, showWhen:or(isnull(local!VerificationDecision), isnull(ri!listing)), isCollapsible: true ), 
 rule!SHA_readonlyinterface() ) 
 )

Dave Lewis · Answer

Please try using the Insert Code option on the Insert menu below your post. That could make your code WAY more readable. 
 It could just be the part of "Student Status" you have commented out. 
 Secondly, what first caught my eye is that you use queryLogicalExpression with an AND operator, and then within the filters, you only have 1 filter. That's entirely pointless. Don't even use the logicalExpression input, leave it blank if you only have one filter. I don't rightly know how the AND operator is supposed to behave with only one filter. But you can just use the filter input instead. You actually do just that elsewhere in your code. Only when you have multiple filters to manage need you bother with logicalExpression. 
 You also BOTH use ignoreFiltersWithNullValues AND manually create the same logic in your applyWhen. I would suggest trying one method of null safety, and then the other, then see what results you get. 
 If these don't yield fruit, then one at a time build each of your local variables, then test test test and make sure all your local variables are being populated correctly. Isolate the individual parts so you can find the part with the error. It could be local!listingDetails, or local!ammenityDetails, or both as far as I know, or something else.

Mike Schmitt · Answer

I second David's suggestion that you use the Insert Code Box when pasting long SAIL/Expression code like this. It's nearly unreadable in this format, whereas the Code Box preserves indentation while also using a more readable font. 
 Per your query error: check tomcat-stdout.log as this might have more exact details as to why it's failing in this case. My guess is that your unfiltered query is pulling back row(s) with invalid data. The one thing I've seen in the past that will definitely cause an error similar to this, is when there are DATE columns and one or more rows being returned by the query have a value of "0000-00-00" (instead of either a valid date or NULL value). It might be worth your time to do a quick scan of your DB to see if any dates / datetimes have such an invalid value.

Unexpected error executing query (type: [SHANewListDT5369], query: [queryentity expression], order by: [[Sort[listingId desc]]], filters:[null])

Top Replies