Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.