Hi Folks,
I've been trying to come up with a viable solution to this for a while. This is a very basic example of what my data sample is at the moment:
ORD_NUM ORD_LINE_NUM ORD_DESC ITEM_NUM PRICE VENDOR DESTINATION DELIVERY_DATE
---------- -------------- ---------- -------- --------- --------- ----------- -------------
0000098456 1 TEST LINE 12345678 12.56 ANN SMITH LONDON 19-AUG-2009
0000098456 2 TEST LINE 12345678 12.56 ANN SMITH LONDON 19-AUG-2009
0000098456 3 TEST LINE 12345678 12.56 ANN SMITH LONDON 19-AUG-2009
0000098456 5 TEST LINE 12345678 12.56 ANN SMITH LONDON 19-AUG-2009
0000012345 2 MORE TEST 98765432 45.78 BARRY KAY COVENTRY 22-AUG-2009
0000012345 3 MORE TEST 98765432 45.78 BARRY KAY COVENTRY 22-AUG-2009
0000012345 5 MORE TEST 98765432 45.78 BARRY KAY COVENTRY 22-AUG-2009
0000012345 6 MORE TEST 98765432 45.78 BARRY KAY COVENTRY 22-AUG-2009
However, for the team requesting the output wants it structured as follows, for interfacing to a different system:
HEADER_LINE_FLAG COL_A COL_B COL_C COL_D
---------------- ---------- ---------- ---------- -----------
HEAD 0000098456 ANN SMITH LONDON 19-AUG-2009
LINE 1 TEST LINE 12345678 12.56
LINE 2 TEST LINE 12345678 12.56
LINE 3 TEST LINE 12345678 12.56
LINE 5 TEST LINE 12345678 12.56
HEAD 0000012345 BARRY KAY COVENTRY 22-AUG-2009
LINE 2 MORE TEST 98765432 45.78
LINE 3 MORE TEST 98765432 45.78
LINE 5 MORE TEST 98765432 45.78
LINE 6 MORE TEST 98765432 45.78
The catch: the team want to extract all the information from a single view with one query for export to a flat file.
I cannot think of a way of doing this apart from creating a pl/sql procedure which will populate a custom table and creating the view over that?
What I would love to do is to manage all of this within the view, if possible. The ordering doesn't matter as long as all the lines for ORDER A come after the header for ORDER A.
Any ideas? Thank you!
Kind Regards,
Pete
Edited by: Pete Mahon on Aug 19, 2009 12:16 PM
Edited by: Pete Mahon on Aug 19, 2009 12:17 PM