Hi,
I need to pull values from DB which does not starts with "ABC" for example.
a!queryEntity(entity: cons!entity,query: a!query(filter: a!queryFilter(field:"office",operator: "not starts with",
value:"ABC"),pagingInfo: a!pagingInfo(startIndex: 1, batchSize: -1)))
But this is ignoring null values, I want null values also to be returned along with other values.
So I just tried fetching only null values to see how it works.
a!queryEntity( entity: cons!entity, query: a!query( filter: a!queryFilter( field:"office", operator: "is null" ), pagingInfo: a!pagingInfo(startIndex: 1, batchSize: -1) ))
this is giving me 0 results. Nots sure what might be the issue. Can anyone please help?
operator "not null" returns correct values, problem is with fetching null only.
Discussion posts and replies are publicly visible
The issue which I can assume is related to the value stored in the DB. If the value is stored as empty string it would not be able to fetch the data with "is null" filter. In order to fetch it, please ensure that the value in DB for the field is stored as NULL (attached for reference)
For your primary use case above, i'm guessing you'd have better luck starting out with a LogicalExpression (set to the "OR" operator) where the filters passed in are "not starts with" and "is null". That should make the query return both types of DB result.
Also be aware that DB values of "null" versus simply "blank" will also be treated differently by query entity results. It's frustrating but can be worked around.
Hi Mayan, I guess the value in db is null, not an empty string.
When I try to use the "not null" operator, it returns other rows and ignores null, so I guess Appian is able to identify null?
DB: Oracle
Agreed with Mike, and the work around if you want to return an empty string is to do something like this:
logicalExpression: a!queryLogicalExpression( operator: "OR", filters: { a!queryFilter( field:"office", operator: "not starts with", value:"ABC" ), a!queryFilter( field: "office", operator: "is null", ), a!queryFilter( field: "office", operator: "in", value: {""} ) } )