Eclipse Community Forums: BIRT

Home » Archived » BIRT » NULL issue(NULLS)

Wed, 28 July 2021 04: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 17: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.

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

Fri, 30 July 2021 00:58

Eclipse User

It looks like your parameter does not match any cust.ref. Check the values in your parameter.

Previous Topic:	Will vulnerability be addressed in v4.9?
Next Topic:	Create Multiple Filters in an SQL Query - Connecting via JDBC

Goto Forum:

-=] Back to Top [=-

Current Time: Thu Mar 13 15:09:55 EDT 2025

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter