NULL issue [message #1843411] |
Wed, 28 July 2021 08:34  |
Eclipse User |
|
|
|
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.
|
|
|
Re: NULL issue [message #1843425 is a reply to message #1843411] |
Wed, 28 July 2021 21:52   |
Eclipse User |
|
|
|
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.
|
|
|
|
Powered by
FUDForum. Page generated in 0.03431 seconds