Number Formatting 1

[pnad]

I have a check amount field in the DB. The values stored in the DB for the check amount look like this:

2637.500, 150.000, 7128.000, 1320.310 etc.

I need to format this field to look like this:

00000263750, 00000015000, 00000712800, 00000132031.

Basically, I need to display the check amount field as right justified zero filled, implied decimal point and no negatives.

I tried lpad(trunc(a.check_amt, 0), 11, '0') but this does not display anything after the decimal point.

Please help !

 

[jaxtell]

how about

lpad(trunc(a.check_amt * 100, 0), 11, '0')

 

[pnad]

Works beautifully.

Thanks for your quick respons.

 

[SantaMufasa]

PNAD,

Try this solution:

SELECT to_char(chk_amt*1000,'09999999999')
  from <table_name>;

Let us know the outcome.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Sorry...I cross posted with Jaxtell. If your source values all have three positions behind the decimal, then must you not multiply by 1000?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Sorry...my bad...I see that you want the last digit trimmed off anyway.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[pnad]

Hi SantaMufasa,

Thanks for responding.

Are there any performance benefits of using to_char vs lpad and then trunc?

 

[pnad]

Also, what I am trying to achieve is a fixed-length flat file to be FTPd to an outside vendor.

The vendor would like all the detail records at the top and then the summary records. To be specific, the format of the file should like like this:

10043749079320200000000012008012500000010000
10043748679560200000000022008012500000020000
20043756779320200000000022008012500000030000

The '10' rows are the detail rows. The '20' row is the summary row which has the count of the checks in the '10' rows and also the sum of the check amounts in the '10' rows.

Even though, the length of the lines is the same (140 characters), the select queries have different number of result columns so I am unable to use a UNION. Is there any other way to do this?

Please advise.

 

[jaxtell]

Can you provide some code? I'm thinking that you can concatenate all the fields into 1, then both selects would have the same number of fields returned and you'd be able to union them.

 

[pnad]

select '10' || '047' || '1234512345' ||
rpad(substr(a.check_number, 2, 10) || ' ', 10) ||
rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
rpad(lpad(trunc(a.check_amt * 100, 0), 11, '0') || ' ', 11),
' ' || 'V' ||
' '
from table a, table b
and a.tran_id = b.tran_id

UNION

select '20'||'047'||'1234512345'||
count(a.check_number)
from table a, table b
and a.tran_id = b.tran_id

 

[jaxtell]

how about

select '10' || '047' || '1234512345' ||
       rpad(substr(a.check_number, 2, 10) || ' ', 10) ||
       rpad(to_char(a.check_date, 'yyyymmdd') || ' ', 8) ||
       rpad(lpad(trunc(a.check_amt * 100, 0), 11, '0') || ' ', 11), as col1
       '               ' || 'V' ||
       '                                                                                ' as col2
  from table a, table b
  and a.tran_id = b.tran_id

UNION

select '20'||'047'||'1234512345'||
count(a.check_number) as col1, null as col2
from table a, table b
   and a.tran_id = b.tran_id

 

[pnad]

That works great.

Thanks for all your help !

 

[SantaMufasa]

Are there any performance benefits of using to_char vs lpad and then trunc?

Yes, I tested the two alternatives with this script:

set serveroutput on format wrap size 1000000
accept loops prompt "Enter the number of times to loop: "
set verify off
declare
    start_time timestamp;
    hold_val varchar2(40);
    procedure p (x varchar2) is
        begin
            dbms_output.put_line(x);
        end;
begin
    start_time := systimestamp;
    p('Start Time of trunc/lpad: '||start_time);
    for i in 1..&loops loop
        hold_val := lpad(trunc(to_char
(systimestamp,'yyyyddmmhh24miss.ff3') * 100, 0), 18, '0');
    end loop;
    p('  End Time of trunc/lpad: '||systimestamp||
      '. Elapsed = '||to_char(systimestamp-start_time));
    start_time := systimestamp;
    p('Start Time of trunc/lpad: '||start_time);
    for i in 1..&loops loop
        hold_val := to_char
(to_char(systimestamp,'yyyyddmmhh24miss.ff3')*100,'09999999999');
    end loop;
    p('  End Time of trunc/lpad: '||systimestamp||
      '. Elapsed = '||to_char(systimestamp-start_time));
end;
/

...and then ran the script with these results:

SQL> @tt_457
Enter the number of times to loop: 1000000
Start Time of trunc/lpad: 31-JAN-08 02.50.24.883000 PM
  End Time of trunc/lpad: 31-JAN-08 02.52.30.353000 PM
      Elapsed = +000000000 00:02:05.470000000
Start Time of to_char   : 31-JAN-08 02.52.30.353000 PM
  End Time of to_char   : 31-JAN-08 02.54.37.576000 PM
      Elapsed = +000000000 00:02:07.223000000

This shows that for 100K iterations:

it took 125.47 seconds for TRUNC + LPAD;
it took 127.223 seconds for TO_CHAR
TO_CHAR took 1.75 seconds (1.4%) longer.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]