Skip to main content
K
kalaiselvil0001
December 21, 2022
Question

null filter does not work in a!queryFilter()

  • December 21, 2022
  • 4 replies
  • 6 views

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.

4 replies

M
mayankn0499
December 21, 2022

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)

K
kalaiselvil0001Author
December 21, 2022

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 

mikes0011
mikes0011
Brainy
December 21, 2022

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.

The Expression Guru
    peter.lewis
    peter.lewis
    Employee
    December 22, 2022

    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: {""}
    )
  }
)

      Terms & ConditionsAccessibility statement