How to display '0' where no data found in PL/SQL Oracle Reports

Question

I have an Oracle Report (11g) in which I have selected a number field containing amount and have mentioned the format mask as 999,999,999,999. The data is displayed as follows.

Data1 25,000
Data2 
Data3 23,000

Now I want to show a simple '0'in front of an empty record (i.e. Data2). I've tried it in adding a Formula Column (PL/SQL), or by initializing a '0' in the property palate of field by defining '0' against "Value if null". But, it doesn't work.

However, when I remove the format mask, value '0' is displayed by both above mentioned ways (by adding a formula column or by defining the value against 'value if null'). But, by doing this, the amount is displayed without format mask (i.e. 25000 and 23000).

How can I achieve the above discussed scenario. I want that the format mask should be there, and there should b '0' in front of empty records as well. In short, I need the following output in Oracle Reports 11g - 64 bit.

Data1 25,000
Data2 0
Data3 23,000

• Philᵀᴹ: NVL() is what you're looking for
• user3332231: I did it with NVL as well. But it doesn't work. I used "NVL(CR.AMOUNT,0)" where "CR" is the table alias. May be it is due to the defined format mask that NVL is not displaying a single 0 in the field. I'll be great full if you recommend me something else. Thank you.
• user1822: Show us your stored procedure
• Rama Kalyan: You can use COALESCE(COLUMN_NAME,0)

Answer 1

The format mask needs to be changed to 999,999,999,990. The 9 format suppresses the value if it is a leading zero, whereas 0 prints it.

Answer 2

Another solution but not a good solution,

select sum(data1), sum(data2), sum(data3) from
(
 select data1, data2, data3 from table1
 UNION ALL
 select 0, 0, 0 from dual
)

Hope this help your problem! I'm also waiting for another great solution to solve this issue.

When there is no data at database, even you write nvl(...,0) still doesn't work. Please expert recommended solution. Thanks!

• Leon: Missing, select 0 as data1, 0 as data2, 0 as data3 from dual
• Andriy M: If you would like to add something to your answer, you can always edit it, although if you are worried about the missing aliases in the UNION ALL's second leg, they are not needed really. The names in the first leg will apply across the entire UNIONed set. What is a problem with this answer is that it returns the output in a different format. The OP's example shows the captions in one column and the figures in another, while yours shows the captions as column names and the figures, consequently, as separate columns. Still, the approach itself makes sense and can probably be adapted anyway.