cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

ADQL showing null rows even when IS NOT NULL is applied

Kenny.Heaton
Creator

I have an ADQL that is trying to count the number of times a Customer is viewed. I have a data collector to add this data to Transactions.

Here is the query:

 

 

 

 

SELECT segments.userData.CustomerName, segments.userData.CustomerNumber, segments.userData.Agreement, count(segments.userData.CustomerName) FROM transactions WHERE application = "TruckCare-CustomerProfile" AND segments.userData.CustomerName IS NOT NULL

 

 

 

But even with the WHERE clause 'CustomerName IS NOT NULL' There is still a line showing up with all nulls. I know there are Transactions that don't have CustomerName, CustomerNumber, or Agreement on them but I am expecting the 'IS NOT NULL' to filter those out.

 
 

table_data.png

 

Any help much appreciated

 

Thanks

1 REPLY 1

Mario.Morelli
Architect

Hi there

 

You need to explicitly set the data types per field for this not to occur:)

 

I just used string for all your fields in your query, however if some are Integers etc., you can update it accordingly

 

SELECT toString(segments.userData.CustomerName), toString(segments.userData.CustomerNumber), toString(segments.userData.Agreement), count(segments.userData.CustomerName) FROM transactions WHERE toString(application) = "TruckCare-CustomerProfile" AND toString(segments.userData.CustomerName) IS NOT NULL



Found something helpful? Click the Accept as Solution button to help others find answers faster.
Liked something? Click the Thumbs Up button.

Learn more about me in the Community Member Spotlight Q & A