Home » SQL & PL/SQL » SQL & PL/SQL » Merging records in an output file (Oracle 10)
| Merging records in an output file [message #430855] |
Thu, 12 November 2009 10:23  |
clancypc
Messages: 36
Registered: December 2006
|
Member |
|
|
I have an existing sql that outputs a master account line line followed by all its associated cli information,then the next account line until all the data has been exported. So the output looks something like:
01 AccountNo AccountName Address1 etc
02 AccountNo CLI Start Date etc
02 AccountNo Cli Start Date etc
01 AccountNo AccountName Address1 etc
The sql for this is:
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(nvl(acc_bill_cycle, ' '),2, ' '),
acc_number || '1' as my_order
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') ,
cli_acc_number || '2' as my_order
from cli_inf
order by 2
Now I have a new record type, an 05 record that I also want to incorporate in this output, but I am not sure how to amend my sql to include it in the output as well. So then I would have something like:
01 AccountNo AccountName Address1 etc
02 AccountNo CLI Start Date etc
02 AccountNo Cli Start Date etc
05 AccountNo Msisdn IMSI etc
05 AccountNo Msisdn IMSI etc
01 AccountNo AccountName Address1 etc
The basic sql for selecting the fields in the new 05 record is eay enough. It will be something like:
SELECT '05' || acc_number ||
rpad(msisdn, 16, ' ') ||
rpad(imsi, 15, ' ') ||
rpad(imei, 15, ' ') ||
TO_CHAR( cli_start_date, 'YYYYMMDD') ||
nvl( TO_CHAR(cli_stop_date, 'YYYYMMDD'), '99999999') ,
cli_acc_number || '3' as my_order
from fmc_cli_inf
order by 2
Is it possible to do what I want in sql, or would I be better maybe in developing it in a procedural language or pl/sql?
Thanks
|
|
|
|
|
|
|
|
|
|
| Re: Merging records in an output file [message #430932 is a reply to message #430931] |
Fri, 13 November 2009 04:43  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
You can do as many UNIONS, INTERSECTS or MINUSES as you like.
Unless the individual queries that you're using return duplicate rows, the rows returned by your UNION statements will all be unique, so you could replace the UNION statements with UNION ALL statements, saving a couple of SORT operations.
|
|
|
|
Goto Forum:
Current Time: Wed Nov 19 04:40:58 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
