Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
share|improve this question
NVL() is what you're looking for – Phil Feb 20 '14 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. – user3332231 Feb 21 '14 at 5:40

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.