null Value in Prompt & Output

[akhattri]

Hello All,
I am trying to create a report in which there are null values in the prompt query item. I first used the Data Format property to change the "Missing Value Property" to Null. However, that did not display the "Null" in prompt. SO I tried the Coalesce function Coalesce(Dataitem,'Null') which did give the value to be null in prompt. However, when I run the report, it returns no rows for that selected NUll value. I am not really sure, where am I going wrong & why does the select of Null returns no rows in the final report page.

Any inputs will be helpful.

Thanks.

[167505]

Hi..

Please check in the database end, whether there is any data for the null values..
in your case, if your prompt is a productname , then try writing a query in the DB end like

select * from product_table where productname is NULL

maybe , in ur case there maynot be any data which is null in the db end.. to make sure that, take the native query  and run it in toad to verify the data

cheers,
Vanthian

[akhattri]

Hi,
As I said before, there is data in database with Null values. It is only when I select the "Null" value from prompt(which is generated after using coalesce function), it is unable to select the corresponding nulls from the database. It somehow seems to be unable to map the two null values from prompt to report.

Thanks.

[DSR]

Hi,

If you want to see all the null values data in the report....

Then firsr replace null value in the prompt with any value....like below..

If (a is null ) then 'x' else a

You need to write same kind of condition in the detail query as well...

where you are going to filter the query with the null values...

And again if you want to display only null value in the report then use an extra data item for desplaying the null value with the original column.

regards,
sanjeev