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
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
  • 1
    NVL() is what you're looking for
    – Philᵀᴹ
    Feb 20, 2014 at 13:37
  • 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. Feb 21, 2014 at 5:40
  • 1
    Show us your stored procedure
    – user1822
    Jan 25, 2017 at 21:33
  • You can use COALESCE(COLUMN_NAME,0) Dec 3, 2017 at 20:36

2 Answers 2


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.


Another solution but not a good solution,

select sum(data1), sum(data2), sum(data3) from
  select data1, data2, data3 from table1
  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!

  • Missing, select 0 as data1, 0 as data2, 0 as data3 from dual
    – Leon
    Jan 25, 2017 at 2:49
  • 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.
    – Andriy M
    Jan 25, 2017 at 9:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.