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 Go to next message
clancypc
Messages: 35
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 #430857 is a reply to message #430855] Thu, 12 November 2009 10:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post a working Test case: create table and insert statements along with the result you want with these data.

Please properly indent the code.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel
Re: Merging records in an output file [message #430867 is a reply to message #430855] Thu, 12 November 2009 12:12 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
I know I must be missing something, but wat can you not just add another UNION ALL to add the query for the '05' record and then order by 2,1
Re: Merging records in an output file [message #430931 is a reply to message #430867] Fri, 13 November 2009 04:33 Go to previous messageGo to next message
clancypc
Messages: 35
Registered: December 2006
Member
That worked thanks. I didnt know you could do two unions.
Re: Merging records in an output file [message #430932 is a reply to message #430931] Fri, 13 November 2009 04:43 Go to previous message
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.
Previous Topic: Usage of INSTR Function
Next Topic: Group By
Goto Forum:
  


Current Time: Fri Dec 09 02:07:26 CST 2016

Total time taken to generate the page: 0.07635 seconds