Link to home
Create AccountLog in
Avatar of futureDBA
futureDBA

asked on

Advanced Query, Special formatting (may require plsql)

I am not sure that the following can be achieved with just a query, any help and direction is greatly appreciated.


I have data on a table called "FINAL" that I need to import into a remote DB2 database in a specific format.

This is the oracle table where the data resides

FINAL TABLE
Order_Date	CUST_NUM	PROD_NUM	Route_NUM	QTY
13-Nov-12	92062		11836		545		70
13-Nov-12	92062		11461		545		70
13-Nov-12	91798		11836		545		210
13-Nov-12	91798		11461		545		140
13-Nov-12	91798		11662		545		70
13-Nov-12	91453		11836		838		70
13-Nov-12	91453		11461		838		70
13-Nov-12	91453		11270		838		70
13-Nov-12	91800		11836		545		420
13-Nov-12	91800		11461		545		70
13-Nov-12	91800		11662		545		70

Open in new window



I need to write a query that gives me the above data in this format. Any row that has a 1 in T_HD_RECID1, has a date, and a customer number is the header, the following rows are the detail of the order. Any group of records that correspond to 1 customer need to have a squence (look at unique order sequence column)

Route_num	Unique Order Sequence	T_HD_RECID1	Product Number	Customer Number		Date		Quantity
545		28			1		0		92062			13-Nov-12	0
545		28			0		11836		0			0		70
545		28			0		11461		0			0		70
545		29			1		0		91798			11/13/2012	0
545		29			0		11836		0			0		210
545		29			0		11461		0			0		140
545		29			0		11662		0			0		70
838		30			1		0		91453			11/13/2012	0
838		30			0		11836		0			0		70
838		30			0		11461		0			0		70
838		30			0		11270		0			0		70
545		31			1		0		91800			11/13/2012	0
545		31			0		11836		0			0		420
545		31			0		11461		0			0		70
545		31			0		11662		0			0		70

Open in new window

Avatar of Am P
Am P
Flag of India image

Follow the below steps to generate the query..

1. write down query # 1 that will be a retrieve the data from FINAL table by grouping the Route_NUM, Order_Date, CUST_NUM and keep the other values default..
ex.
Select Route_num,      <TODO:Sequence>,      1 as T_HD_RECID1,      0 as Product Number,      Customer Number, Date, 0 as Quantity from Final group by Route_num, Date, Customer Number

2. write down query # 2 that will be a retrieve the detail data from FINAL table by grouping the Route_NUM, Order_Date, CUST_NUM and keep the other values default..
ex.
Select Route_num,      <TODO:Sequence>,      0 as T_HD_RECID1,      Product Number, 0 as Customer Number, null as Date, Quantity from Final

3. Now do union of above queries and retrive the rows as per your need. Do sort, if required (ie, Route_num, <TODO:Sequence>, T_HD_RECID1)

Hope, you understand the logic to get the rows.
Avatar of futureDBA
futureDBA

ASKER

very straight forward, i have written both queries as per your instructions.

I am still confused on how to go about <TODO:Sequence>
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account