Skip to main content
banshp0001
banshp0001
June 10, 2021
Question

Query Entity fails when using operator "include" for the column having NULL value in database

  • June 10, 2021
  • 6 replies
  • 0 views

Query Entity fails when using operator "include" for the column having NULL value in database. I am using view to retrieve the data.

This is working fine when I remove the query filter having "include" operator.

Seems "include" is having problem with columns having null values specially in case of view.

Anyone, please suggest some solution around this.

Exception details:

Expression evaluation error at function a!queryEntity [line 3]: An error occurred while retrieving the data. Details: Unexpected error executing query (type: [TWMVwDocumentMappingDT4091], query: [queryentity expression], order by: [[Sort[isDeleted asc]]], filters:[((isDeleted = TypedValue[it=26,v=0]) AND (siteId includes TypedValue[it=3,v=AB41855]))])

    6 replies

    csteward
    csteward
    June 10, 2021

    Hi Bansh, I cannot replicate this functionality - a few tests with a!queryEntity() over data stores connected to views are successfully allowing me to utilize the "includes" operator on both columns that contain nulls, and are completely null (all rows).  On occasion, views can perform type casting to create some odd behavior. 

    How is the siteId column defined in your view?

    Are all rows null or only some?

    Is it possible to test the view using CAST or a CASE statement to convert nulls to empty strings, such as:

    case
	when siteId is null then ''
	else siteId
end as 'siteId'

    banshp0001
    banshp0001Author
    June 10, 2021

    Tried with this way as well. Still problem is not resolved. Even I used IsNull(col1, '') but not working

    csteward
    csteward
    June 10, 2021

    If you change the "includes" operator to "=", does the error persist? 

    How about if you test a!queryEntity() with only the filter utilizing "includes"? 

    Is it possible to share your a!queryEntity() setup?

    joshf713
    joshf713
    June 10, 2021

    This may be a wild guess, but does it have anything to do with your sort? Maybe the values in "isDeleted" are not as expected. Change that and try it out. 

    Terms & ConditionsAccessibility statement