Home » SQL & PL/SQL » SQL & PL/SQL » Formatted output from Oracle
Formatted output from Oracle [message #234965] Thu, 03 May 2007 10:07 Go to next message
clancypc
Messages: 35
Registered: December 2006
Member
Hi,
I am trying to get formatted output from an oracle database and I cant figure out how to do it.
What I want is an account record on one line followed by all its associated telephone numbers (cli), then another account record, etc.
I can do the individual sql to produce only the account records in the correct format or only the cli records but I dont know how to interleave them. How do I do that?

In case anyone is interested the sql for both types of records is:
    
    set linesize 500
    set pagesize 0
    set colsep '|'
    SELECT '01', acc_number, rpad(acc_name,25,' '),
    rpad(nvl(acc_address1,' '),25,' '),
    rpad(nvl(acc_address2,' '),25,' '),
    rpad(nvl(acc_address3,' '),25,' '),
    rpad(nvl(acc_address4,' '),25,' '),
    rpad(nvl(acc_postcode,' '),8,' '),
    TO_CHAR(acc_start_date, 'YYYYMMDD'),
    nvl(TO_CHAR(acc_stop_date, 'YYYYMMDD'),'99999999'),
    acc_toscat,
    rpad(acc_bill_cycle,2,' ')
    FROM acc_inf


Then I could use sed to edit the file and remove all the '|' to remove the pesky column seperator.
And similar sql for the cli records:

    
    set linesize 500
    set pagesize 0
    set colsep '|'
    SELECT '02', cli_acc_number,
    RPAD(cli_bill_number,16,' '),
    cli_bill_type,
    TO_CHAR(cli_start_date, 'YYYYMMDD'),
    nvl(TO_CHAR(cli_stop_date, 'YYYYMMDD'),'99999999')
    FROM cli_inf
    ORDER BY 1,2 ASC;


and again editing with sed will let me remove the pipe characers.

Any help will be greatly appreciated.
Thanks
Peter Clancy
Re: Formatted output from Oracle [message #234974 is a reply to message #234965] Thu, 03 May 2007 10:38 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
concatenate the columns to form one columnThen union the two queries.
add a column in which you lpad (cli_)acc_number to its max length with zeroes. Concatenate that with '1' or '2' and alias it as my_order.
Order by that column.
column my_order noprint will suppress output of it

[Edit: More robust]

[Updated on: Thu, 03 May 2007 10:40]

Report message to a moderator

Re: Formatted output from Oracle [message #234984 is a reply to message #234974] Thu, 03 May 2007 11:31 Go to previous messageGo to next message
clancypc
Messages: 35
Registered: December 2006
Member
Frank,
Not quite sure I've grasped what you mean, I have tried this:

SELECT '01'|| acc_number|| rpad(acc_name,25,' ')||
    rpad(nvl(acc_address1,' '),25,' ')||
    rpad(nvl(acc_address2,' '),25,' ')||
    rpad(nvl(acc_address3,' '),25,' ')||
    rpad(nvl(acc_address4,' '),25,' ')||
    rpad(nvl(acc_postcode,' '),8,' ')||
    TO_CHAR(acc_start_date, 'YYYYMMDD')||
    nvl(TO_CHAR(acc_stop_date, 'YYYYMMDD'),'99999999')||
    acc_toscat||
    rpad(acc_bill_cycle,2,' ')
    FROM acc_inf
UNION
    SELECT '02'|| cli_acc_number||
    RPAD(cli_bill_number,16,' ')||
    cli_bill_type||
    TO_CHAR(cli_start_date, 'YYYYMMDD')||
    nvl(TO_CHAR(cli_stop_date, 'YYYYMMDD'),'99999999')
    FROM cli_inf, acc_inf
    WHERE cli_inf.cli_acc_number = acc_inf.acc_number



But I'm just getting the all the 01 records, presumably if I had waited long enough it would have been followed by all the 02 records.
Whereas where I want is:
01Accout details
02(zero or more) cli details
02cli details
01Account details
02cli details
etc...

What I did forget to mention is that

cli_inf.cli_acc_number = acc_inf.acc_number

Dont know if that helps

Thanks
Peter
Re: Formatted output from Oracle [message #235020 is a reply to message #234984] Thu, 03 May 2007 15:00 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
I don't know the max-size of your acc_number column, but suppose it is 10.
You want to order your records by acc-number, but per acc-number, the 01 record should come first and then the 02 record, so what we do is we concatenate these two:
column my_order noprint
select <your concatenated column>
,      to_char(acc_number, '0000000000')||'1' as my_order
from   acc_inf
union  all
select ...
,      to_char(cli_acc_number, '0000000000')||'2'
from   cli_inf

The padding with zeroes is to make sure the acc_numbers will be ordered as numbers. Therefore, they have to be lpadded to fit the biggest number.

Hope I made it clearer.
Previous Topic: Query Help
Next Topic: Loading XML Type into ProC Variable
Goto Forum:
  


Current Time: Sat Dec 03 05:55:02 CST 2016

Total time taken to generate the page: 0.12772 seconds