Formatting NULL and regular numbers [message #273717] |
Thu, 11 October 2007 08:41  |
lmayer
Messages: 8 Registered: December 2006
|
Junior Member |
|
|
Morning,
I have a number I'm trying to format to 12 places with leading zeros. It works great if its a NULL but if its a number its drops the leading zero. this is what I have and the results (I've thrown the number 638 here but it uses a field from a database in the original query)
select to_char( '638','000000000D00') Result1,
lpad(nvl(to_char(to_number('638'),'000000000D00'),'0'),12,'0') Result2,
lpad(nvl(to_char(to_number(NULL),'000000000D00'),'0'),12,'0') Result3,
lpad(nvl(to_char(to_number('638')),'0'),12,'0')Result4
from dual
Result1----000000638.00 --This is how I want it to look.
Result1---- 000000638.0 --this is the problem child
Result1----000000000000 --this is just a test
Result1----000000000638 --this is how it is right now without the decimal
Thanks for any help you can offer.
Laura
[Updated on: Thu, 11 October 2007 08:44] Report message to a moderator
|
|
|
|
Re: Formatting NULL and regular numbers [message #273726 is a reply to message #273719] |
Thu, 11 October 2007 09:02   |
lmayer
Messages: 8 Registered: December 2006
|
Junior Member |
|
|
Thanks Michel,
I'm afraid I don't know how to to translate this to a sql statement.
For example my script goes something like this:
||'02'
||rpad(to_char(AR_TRANSACTIONS_SLM.BILL_DATE,'MMDDYYYY'),15)
||rpad(to_char(AR_TRANSACTIONS_SLM.DUE_DATE,'MMDDYYYY'),15)
||lpad(nvl(to_char(to_number(c.CHARGES),'000000000.00'),'0'),12,'0')
||lpad(nvl(to_char(to_number(d.payments),'000000000.00'),'0'),12,'0')
||rpad(' ',12)
||rpad(' ',12)
||rpad(' ',12)
I'm trying to make the charges and the payments have 2 decimal points and whatever leading zeros to make 12 bytes.
Sorry for being obtuse...The problem is that is is an export to a text file that a COBOL program (no I'm not kidding) uses to parse and it has to be at the exact line for the format.
Thanks again,
Laura
[Updated on: Thu, 11 October 2007 10:14] by Moderator Report message to a moderator
|
|
|
|