A null value passed as a parameter

lenburt

Hi there - i am using BIRT 2.6.2 -- this seems like it should be easy but i have not found a solution. I am invoking a child report on MouseClick passing several parameters which are used by the child report in a SQL query denoted with ?'s. It works fine when i pass values in the strings but when one of them is null, a prompt pops up even though i designate the parameter as not required. Null is a valid value for the database column so i need to somehow get it passed through to the query....any thoughts out there?

regards, len

Hans_vd

Hi Len,<br />
<br />
What's the popup saying?<br />
<br />
Also: even when you manage to have the null value passed through to your query, you won't get any results. Null values in a database can only be selected (or left out of your selection) by using the IS NULL and IS NOT NULL operators.<br />
<br />
What you can do to deal with empty parameters is write your SQL like this:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>...
WHERE ( your_col = ? OR (your_col IS NULL AND ? IS NULL) )</pre>
or<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>...
WHERE nvl(your_col, 'some unused value') = nvl(?, 'some unused value')</pre>
Regards<br />
Hans