2

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
  • 1
    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
  • 1
    Show us your stored procedure – a_horse_with_no_name Jan 25 '17 at 21:33
  • You can use COALESCE(COLUMN_NAME,0) – Rama Kalyan Dec 3 '17 at 20:36
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.

|improve this answer|||||
0

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!

|improve this answer|||||
  • Missing, select 0 as data1, 0 as data2, 0 as data3 from dual – Leon Jan 25 '17 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 '17 at 9:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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