ADQL showing null rows even when IS NOT NULL is applied

[Kenny.Heaton]

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.

Any help much appreciated

Thanks

[Mario.Morelli · Accepted solution]

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