Eclipse Community Forums: BIRT

Help

Home

Home » Archived » BIRT » NULL issue(NULLS)

Show: Today's Messages :: Show Polls :: Message Navigator

NULL issue [message #1843411]

Wed, 28 July 2021 08:34

Mike Delaney

Messages: 5
Registered: July 2021

Junior Member

Hi All,

I have a where clause in my birt report:

WHERE Cust.Ref LIKE ?

This clause seems to be impacting my report. When i run the report and filter on a different value (Nominal Code) i get 0 results where it should return 34 results. I did some investigate and found that it was the cust.ref clause causing the report to exclude null values.

I amended the clause to:
WHERE (Cust.Ref LIKE ? OR IS NULL)

This fixed the nominal code issue and gave me all results however, when i filter by customer this time it gives me the results for that customer as well as all data where the customer is a null value.

Can anyone help resolve this?

Thanks.

Report message to a moderator

Re: NULL issue [message #1843425 is a reply to message #1843411]

Wed, 28 July 2021 21:52

Robert Jones

Messages: 10
Registered: June 2021

Junior Member

Mike,

not a BIRT issue, but I will offer some advice. You may be confused by NULL? You have to think of the NULL as its own value - or non-value.

If you were buying fruit, and wanted apples and oranges, would you want every other fruit that the shop keeping hadn't labeled or didn't know if it was a fruit or not? NULL is the basket where all the "I don't know what it is or haven't bothered identifying it" goes. Where fruit = 'applies' OR NULL-basket rewards you with apples and the NULL basket because you match both criteria.

So you have some choices:
1. Find out what those NULLs are supposed to be and fix your data.
2. Decide that NULL is valid - not assigned - and deliberately select it
3. Decide that you don't want unknowns and ignore NULLs

in SQL you should be able to do LIKE NULL to match NULL, you could try explicitly this first, then you should be able to use LIKE ? making sure you have a parameter that evaluates to NULL - and not %NULL% which would be treated as a string.

Report message to a moderator