cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

Community Tip - Learn all about PTC Community Badges. Engage with PTC and see how many you can earn! X

Operator Advisor - Unable to query for null values using JSON filters

azoorob3
13-Aquamarine

Operator Advisor - Unable to query for null values using JSON filters

Hey Everyone,

 

I am currently using Operator Advisor 8.5.1.

 

I am unable to query for rows containing null values when using the Operator Advisor queries and JSON filters. When I query the tables directly using sql from the database thing, I am able to pull rows containing null values. However, when using OA queries/JSON filters, there is no way for me to pull these null values. I have attached a word document showing different filters I tried + the error messages.

 

Does anyone have an idea how I can pull rows containing  null/blank values from postgres database tables while using the Operator advisor queries? The MissingValue filter is not supported, which is what I'd use when querying a data table or stream. 

 

I want to avoid writing direct sql queries to do this if at all possible. 

 

Thanks,


Andrew

ACCEPTED SOLUTION

Accepted Solutions
azoorob3
13-Aquamarine
(To:azoorob3)

Hi All,

 

I was able to figure it out with the help of the support team. For Operator Advisor queries using the database Query service, you need to query for null values this way:

 

{

    "filters": {

        "fieldName": "endTime",

        "type": "MISSINGVALUE"

    }

}

 

MISSINGVALUE needs to be listed in all capitals, despite it being shown as MissingValue in the query parameter ThingWorx documentation. 

 

Andrew

View solution in original post

2 REPLIES 2

Hi @azoorob3 ,

 

PTC Support services is working with you on the support case for the same issue reported by you.

We will post the solution once the case is resolved.

 

Thanks,

Himanshu

azoorob3
13-Aquamarine
(To:azoorob3)

Hi All,

 

I was able to figure it out with the help of the support team. For Operator Advisor queries using the database Query service, you need to query for null values this way:

 

{

    "filters": {

        "fieldName": "endTime",

        "type": "MISSINGVALUE"

    }

}

 

MISSINGVALUE needs to be listed in all capitals, despite it being shown as MissingValue in the query parameter ThingWorx documentation. 

 

Andrew

Announcements


Top Tags