Home » SQL & PL/SQL » SQL & PL/SQL » Formatting NULL and regular numbers
Formatting NULL and regular numbers [message #273717] Thu, 11 October 2007 08:41 Go to next message
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 #273719 is a reply to message #273717] Thu, 11 October 2007 08:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with data as (select 638 v from dual union all select to_number(null) from dual)
  2  select to_char(nvl(v,0),'fm0000000000D00') with_dec,
  3         to_char(nvl(v,0),'fm000000000000') without_dec
  4  from data;
WITH_DEC       WITHOUT_DEC
-------------- -------------
0000000638.00  000000000638
0000000000.00  000000000000

2 rows selected.

Regards
Michel
Re: Formatting NULL and regular numbers [message #273726 is a reply to message #273719] Thu, 11 October 2007 09:02 Go to previous messageGo to next message
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

Re: Formatting NULL and regular numbers [message #273753 is a reply to message #273726] Thu, 11 October 2007 10:15 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is this not what I posted?
What does not work in what I posted?

Regards
Michel
Previous Topic: Count the characters
Next Topic: How to get complete SQL given the hash_value
Goto Forum:
  


Current Time: Fri Dec 02 17:02:40 CST 2016

Total time taken to generate the page: 0.33889 seconds