Home » SQL & PL/SQL » SQL & PL/SQL » Format result set of a union (DB 10g)
Format result set of a union [message #655344] Mon, 29 August 2016 08:17 Go to next message
adfnewbie
Messages: 54
Registered: January 2016
Member
hi guys, need help on below case:

My Query:

select col1,col2,col3,col4,col5 from t1
union
select col1,col2,col3,col4,col5 from t2
union
select col1,col2,col3,col4,col5 from t3

My output: first 2 lines are from t1, next 4 from t2 and last 4 from t3

col1	col2	         col3	   col4	      col5
---------------------------------------------------
100	Samsung	        Newyork	    US	        50
200	Sony	        London	    UK 	        60
100.1	TV	       42inches    LED	        10
100.2	WashingM/C	 7kg	  Top Load	20
200.1	Headphones	 30db	   In-ear	50
200.2	Speakers	 500W	   Dolby	10
100.11	Delivered	 Ground	   FedEx	1
100.21	Delivered	 Air	    DHL	        1
200.11	In-Transit	 Ground	  Orient Exp	2
200.21	Delivered	 Air	    AT&T	1

How do I order these results into the one like below:

col1
----

100
100.1
100.11
100.2
100.21
200
200.1
200.11
200.2
200.21

Basically-- 100,200 are order numbers -- 100.1,100.2,200.1,200.2 are lines for those orders ---- 100.11,100.21,200.11,200.21 are shipment details for those order lines. I require the result set to be in the format:

row1 -  order1
row2 -  order line1.1
row3 -  shipments for line1.1
row4 -  order line1.2
row5 -  shipments for line1.2
row6 -  order2
row7 -  order line2.1
row8 -  shipments for line2.1
row9 -  order line2.1
row10 - shipments for line2.1
rown -  and so on.....

Any assistance is highly appreciated! Thanks!


[mod-edit: code tags added by bb]


[Updated on: Mon, 29 August 2016 16:29] by Moderator

Report message to a moderator

Re: Format result set of a union [message #655345 is a reply to message #655344] Mon, 29 August 2016 08:20 Go to previous messageGo to next message
adfnewbie
Messages: 54
Registered: January 2016
Member
Sorry for the messy result set. Please consider <space> as column value separator
Re: Format result set of a union [message #655346 is a reply to message #655345] Mon, 29 August 2016 08:28 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/mv/msg/201122/652346/#msg_652346
Re: Format result set of a union [message #655350 is a reply to message #655346] Mon, 29 August 2016 08:35 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
If col1 is a number then

select col1,col2,col3,col4,col5 from t1
union
select col1,col2,col3,col4,col5 from t2
union
select col1,col2,col3,col4,col5 from t3
order by 1;

if col1 is a string then

select col1,col2,col3,col4,col5
from
(
select col1,col2,col3,col4,col5 from t1
union
select col1,col2,col3,col4,col5 from t2
union
select col1,col2,col3,col4,col5 from t3)
order by to_number(col1);
Re: Format result set of a union [message #655351 is a reply to message #655350] Mon, 29 August 2016 08:37 Go to previous messageGo to next message
adfnewbie
Messages: 54
Registered: January 2016
Member
BlackSwan,

The issue with formats is that when I type the message it shows a different format and it changes after I post the message. I don't find a way of stopping it
Re: Format result set of a union [message #655352 is a reply to message #655351] Mon, 29 August 2016 08:38 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Re: Format result set of a union [message #655353 is a reply to message #655351] Mon, 29 August 2016 08:39 Go to previous messageGo to next message
adfnewbie
Messages: 54
Registered: January 2016
Member
Hi Bill B,

Thanks for the response, but that column will not be in the order which I have shown, col1 value can contain any value. For eg- for an order, order number can be greater than line number/shipment number. A simple order by wont work
Re: Format result set of a union [message #655354 is a reply to message #655353] Mon, 29 August 2016 08:39 Go to previous messageGo to next message
adfnewbie
Messages: 54
Registered: January 2016
Member
I have read that and it did not help me. It is for coding. Not for texts
Re: Format result set of a union [message #655355 is a reply to message #655354] Mon, 29 August 2016 08:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
You can lead some folks to knowledge, but you can't make them think.
How sad! Sad
Re: Format result set of a union [message #655356 is a reply to message #655355] Mon, 29 August 2016 08:43 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

col1	col2	         col3	   col4	      col5
---------------------------------------------------
100	Samsung	        Newyork	    US	        50
200	Sony	        London	    UK 	        60
100.1	TV	       42inches    LED	        10
100.2	WashingM/C	 7kg	  Top Load	20
200.1	Headphones	 30db	   In-ear	50
200.2	Speakers	 500W	   Dolby	10
100.11	Delivered	 Ground	   FedEx	1
100.21	Delivered	 Air	    DHL	        1
200.11	In-Transit	 Ground	  Orient Exp	2
200.21	Delivered	 Air	    AT&T	1

Re: Format result set of a union [message #655357 is a reply to message #655356] Mon, 29 August 2016 08:46 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Since I gave you a response for the data you showed, If the order number in col1 is not 100 and the detail are not 100.1 (for example) then what is actually in the column to be sorted on?
Re: Format result set of a union [message #655358 is a reply to message #655356] Mon, 29 August 2016 08:51 Go to previous messageGo to next message
adfnewbie
Messages: 54
Registered: January 2016
Member
Thanks a lot for the nice line! Yes it's very sad!
Re: Format result set of a union [message #655359 is a reply to message #655358] Mon, 29 August 2016 08:56 Go to previous messageGo to next message
adfnewbie
Messages: 54
Registered: January 2016
Member
Bill,
That data was just sample. I don't need to order it on values because I can't be sure of values. lets not talk data. Just assume there are 3 tables.. orders (order_id), lines(line id) and shipments (shipment id). I need order record first and next lines of that order corresponding to that order id and then shipment of the line based on line_id. In short, order_id from orders should be passed to lines and line_id from lines should be passed to shipments.
Re: Format result set of a union [message #655361 is a reply to message #655345] Mon, 29 August 2016 11:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Tue, 07 June 2016 18:25

Quote:
So it's just about declaring and initialising and nothing else just like below?
Yes, please read [How to use [code] tags and make your code easier to read.
Your answer:

adfnewbie wrote on Tue, 07 June 2016 18:46
Thanks! It was a simple one so didn't bother to do the formatting! Will do all the time from now on!

Thanks again!
Re: Format result set of a union [message #655364 is a reply to message #655361] Mon, 29 August 2016 13:29 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
Do you have foreign keys in your table? For example, in the lines the order_id column should be in the row to point back to the parent order. In the shipments table the column line_id and maybe order_id should be included. If the line_id is filled via a sequence you will not need the order_id if it is something like 1,2,3... for each order you will need to include the order_id in the shipments table. If you have these column it is simple with a join to pull out any information that you need. Please paste the actual table descriptions for the three tables into this issue.
Re: Format result set of a union [message #655373 is a reply to message #655344] Mon, 29 August 2016 16:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_12.1.0.2.0> select *
  2  from   (select col1,col2,col3,col4,col5 from t1
  3  	     union
  4  	     select col1,col2,col3,col4,col5 from t2
  5  	     union
  6  	     select col1,col2,col3,col4,col5 from t3)
  7  order by substr
  8  		(col1,
  9  		 1,
 10  		 decode
 11  		   (instr(col1,'.'),
 12  		    0,length(col1),
 13  		    instr(col1,'.'))),
 14  	      substr(col1,instr(col1,'.')+1)
 15  /

COL1   COL2       COL3     COL4             COL5
------ ---------- -------- ---------- ----------
100    Samsung    Newyork  US                 50
100.1  TV         42inches LED                10
100.11 Delivered  Ground   FedEx               1
100.2  WashingM/C 7kg      Top Load           20
100.21 Delivered  Air      DHL                 1
200    Sony       London   UK                 60
200.1  Headphones 30db     In-ear             50
200.11 In-Transit Ground   Orient Exp          2
200.2  Speakers   500W     Dolby              10
200.21 Delivered  Air      AT&T                1

10 rows selected.
Re: Format result set of a union [message #655618 is a reply to message #655373] Tue, 06 September 2016 06:11 Go to previous messageGo to next message
sandeep_orafaq
Messages: 88
Registered: September 2014
Member
This query is again sorting on value, as OP mentioned that the sorting on value is not the one he needs, Unfortunately he has posted data in such manner that it confuses all of us. I think Bill B is right on point. OP must provide the relation between tables. And I am sure if the design guidelines are followed correctly then there must be Foreign keys in Line and shipment tables.
Re: Format result set of a union [message #655627 is a reply to message #655373] Tue, 06 September 2016 08:12 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Barbara,

You must use TO_NUMBER. Compare:

SQL> with t as(
  2            select '1.10' col1 from dual union all
  3            select '1.5' from dual
  4           )
  5  select  *
  6    from  t
  7    order by substr(
  8                    col1,
  9                    1,
 10                    decode(
 11                           instr(col1,'.'),
 12                           0,length(col1),
 13                           instr(col1,'.')
 14                          )
 15                   ),
 16                   substr(col1,instr(col1,'.')+1)
 17  /

COL1
----
1.10
1.5

SQL> with t as(
  2            select '1.10' col1 from dual union all
  3            select '1.5' from dual
  4           )
  5  select  *
  6    from  t
  7    order by to_number(
  8                       substr(
  9                              col1,
 10                              1,
 11                              decode(
 12                                     instr(col1,'.'),
 13                                     0,length(col1),
 14                                     instr(col1,'.')
 15                                    )
 16                             )
 17                      ),
 18              to_number(substr(col1,instr(col1,'.')+1))
 19  /

COL1
----
1.5
1.10

SQL>

And your soloution is limited to two levels. Generic solution is:

ORDER BY REGEXP_REPLACE(
                        REGEXP_REPLACE(
                                       COL1,
                                       '\d+',
                                       LPAD('0',127,'0') || '\1'
                                      ),
                        '\d*(\d{127})',
                        '\1'
                       )

SY.
Re: Format result set of a union [message #655628 is a reply to message #655627] Tue, 06 September 2016 08:15 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
I forgot to mention, 127 is obviously an overkill. We should use max possible length of each part of dotted expression.

SY.
Re: Format result set of a union [message #655634 is a reply to message #655627] Tue, 06 September 2016 15:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Solomon,

The OP asked:

Quote:

How do I order these results into the one like below:

col1
----
100
100.1
100.11
100.2
100.21
200
200.1
200.11
200.2
200.21

Using the following test data, based on the sample data provided:
SCOTT@orcl_12.1.0.2.0> set define off scan off
SCOTT@orcl_12.1.0.2.0> create table t1 as
  2  select '100' col1, 'Samsung' col2, 'Newyork' col3, 'US' col4, 50 col5 from dual union all
  3  select '200' col1, 'Sony'	  col2, 'London'  col3, 'UK' col4, 60 col5 from dual
  4  /

Table created.

SCOTT@orcl_12.1.0.2.0> create table t2 as
  2  select '100.1' col1, 'TV'	  col2, '42inches' col3, 'LED' col4, 10 col5 from dual union all
  3  select '100.2' col1, 'WashingM/C'	  col2, '7kg'  col3, 'Top Load' col4, 20 col5 from dual union all
  4  select '200.1' col1, 'Headphones'	  col2, '30db' col3, 'In-ear' col4, 50 col5 from dual union all
  5  select '200.2' col1, 'Speakers'	col2, '500W'  col3, 'Dolby' col4, 10 col5 from dual
  6  /

Table created.

SCOTT@orcl_12.1.0.2.0> create table t3 as
  2  select '100.11' col1, 'Delivered'	  col2, 'Ground' col3, 'FedEx' col4, 1 col5 from dual union all
  3  select '100.21' col1, 'Delivered'	  col2, 'Air'  col3, 'DHL' col4, 1 col5 from dual union all
  4  select '200.11' col1, 'In-Transit'    col2, 'Ground' col3, 'Orient Exp' col4, 2 col5 from dual union all
  5  select '200.21' col1, 'Delivered'	  col2, 'Air'  col3, 'AT&T' col4, 1 col5 from dual
  6  /

Table created.

My query produces results in the order requested by the OP.
SCOTT@orcl_12.1.0.2.0> select *
  2  from   (select col1,col2,col3,col4,col5 from t1
  3  	     union
  4  	     select col1,col2,col3,col4,col5 from t2
  5  	     union
  6  	     select col1,col2,col3,col4,col5 from t3)
  7  order by substr
  8  		(col1,
  9  		 1,
 10  		 decode
 11  		   (instr(col1,'.'),
 12  		    0,length(col1),
 13  		    instr(col1,'.'))),
 14  	      substr(col1,instr(col1,'.')+1)
 15  /

COL1   COL2       COL3     COL4             COL5
------ ---------- -------- ---------- ----------
100    Samsung    Newyork  US                 50
100.1  TV         42inches LED                10
100.11 Delivered  Ground   FedEx               1
100.2  WashingM/C 7kg      Top Load           20
100.21 Delivered  Air      DHL                 1
200    Sony       London   UK                 60
200.1  Headphones 30db     In-ear             50
200.11 In-Transit Ground   Orient Exp          2
200.2  Speakers   500W     Dolby              10
200.21 Delivered  Air      AT&T                1

10 rows selected.

Your usage of to_number does not result in the OP's desired order:
SCOTT@orcl_12.1.0.2.0> select *
  2  from   (select col1,col2,col3,col4,col5 from t1
  3  	     union
  4  	     select col1,col2,col3,col4,col5 from t2
  5  	     union
  6  	     select col1,col2,col3,col4,col5 from t3)
  7  order by to_number(
  8  			  substr(
  9  				 col1,
 10  				 1,
 11  				 decode(
 12  					instr(col1,'.'),
 13  					0,length(col1),
 14  					instr(col1,'.')
 15  				       )
 16  				)
 17  			 ),
 18  		 to_number(substr(col1,instr(col1,'.')+1))
 19  /

COL1   COL2       COL3     COL4             COL5
------ ---------- -------- ---------- ----------
100.1  TV         42inches LED                10
100.2  WashingM/C 7kg      Top Load           20
100.11 Delivered  Ground   FedEx               1
100.21 Delivered  Air      DHL                 1
100    Samsung    Newyork  US                 50
200.1  Headphones 30db     In-ear             50
200.2  Speakers   500W     Dolby              10
200.11 In-Transit Ground   Orient Exp          2
200.21 Delivered  Air      AT&T                1
200    Sony       London   UK                 60

10 rows selected.

Your generic method also does not produce results in the OP's desired order.
SCOTT@orcl_12.1.0.2.0> select *
  2  from   (select col1,col2,col3,col4,col5 from t1
  3  	     union
  4  	     select col1,col2,col3,col4,col5 from t2
  5  	     union
  6  	     select col1,col2,col3,col4,col5 from t3)
  7  ORDER BY REGEXP_REPLACE(
  8  			     REGEXP_REPLACE(
  9  					    COL1,
 10  					    '\d+',
 11  					    LPAD('0',127,'0') || '\1'
 12  					   ),
 13  			     '\d*(\d{127})',
 14  			     '\1'
 15  			    )
 16  /

COL1   COL2       COL3     COL4             COL5
------ ---------- -------- ---------- ----------
100    Samsung    Newyork  US                 50
200    Sony       London   UK                 60
100.2  WashingM/C 7kg      Top Load           20
200.21 Delivered  Air      AT&T                1
200.1  Headphones 30db     In-ear             50
200.11 In-Transit Ground   Orient Exp          2
200.2  Speakers   500W     Dolby              10
100.11 Delivered  Ground   FedEx               1
100.1  TV         42inches LED                10
100.21 Delivered  Air      DHL                 1

10 rows selected.

[Updated on: Tue, 06 September 2016 15:40]

Report message to a moderator

Re: Format result set of a union [message #655635 is a reply to message #655618] Tue, 06 September 2016 16:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
sandeep_orafaq wrote on Tue, 06 September 2016 04:11
This query is again sorting on value, as OP mentioned that the sorting on value is not the one he needs, Unfortunately he has posted data in such manner that it confuses all of us. I think Bill B is right on point. OP must provide the relation between tables. And I am sure if the design guidelines are followed correctly then there must be Foreign keys in Line and shipment tables.

Sandeep,

Yes, that's correct.

If, for example, there is data like:
SCOTT@orcl_12.1.0.2.0> select * from orders
  2  /

COL COL2    COL3    CO       COL5
--- ------- ------- -- ----------
100 Samsung Newyork US         50
200 Sony    London  UK         60

2 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from lines
  2  /

ORDR COL1  COL2       COL3     COL4           COL5
---- ----- ---------- -------- -------- ----------
100  100.1 TV         42inches LED              10
100  100.2 WashingM/C 7kg      Top Load         20
200  200.1 Headphones 30db     In-ear           50
200  200.2 Speakers   500W     Dolby            10

4 rows selected.

SCOTT@orcl_12.1.0.2.0> select * from details
  2  /

LINE  COL1   COL2       COL3   COL4             COL5
----- ------ ---------- ------ ---------- ----------
100.1 100.11 Delivered  Ground FedEx               1
100.2 100.21 Delivered  Air    DHL                 1
200.1 200.11 In-Transit Ground Orient Exp          2
200.2 200.21 Delivered  Air    AT&T                1

4 rows selected.

Then it could be selected and ordered like so:
SCOTT@orcl_12.1.0.2.0> select col1, col2, col3, col4, col5
  2  from   (select col1 ordr, null line, null details,    col1,  col2,   col3,   col4,   col5
  3  	     from   orders
  4  	     union all
  5  	     select ordr,      col1 line, null details,    col1,  col2,   col3,   col4,   col5
  6  	     from   lines
  7  	     union all
  8  	     select l.ordr,   d.line,	 d.col1 details, d.col1, d.col2, d.col3, d.col4, d.col5
  9  	     from   lines l, details d
 10  	     where  l.col1 = d.line)
 11  order  by ordr, line nulls first, details nulls first
 12  /

COL1   COL2       COL3     COL4             COL5
------ ---------- -------- ---------- ----------
100    Samsung    Newyork  US                 50
100.1  TV         42inches LED                10
100.11 Delivered  Ground   FedEx               1
100.2  WashingM/C 7kg      Top Load           20
100.21 Delivered  Air      DHL                 1
200    Sony       London   UK                 60
200.1  Headphones 30db     In-ear             50
200.11 In-Transit Ground   Orient Exp          2
200.2  Speakers   500W     Dolby              10
200.21 Delivered  Air      AT&T                1

10 rows selected.

[Updated on: Tue, 06 September 2016 16:11]

Report message to a moderator

Re: Format result set of a union [message #655637 is a reply to message #655634] Tue, 06 September 2016 16:42 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Barbara Boehmer wrote on Tue, 06 September 2016 16:38

Your generic method also does not produce results in the OP's desired order.
Oops, I forgot to enclose \d in parenthesis, so there is no \1 and it was taken literally. It should be:

ORDER BY REGEXP_REPLACE(
                        REGEXP_REPLACE(
                                       COL1,
                                       '(\d+)',
                                       LPAD('0',127,'0') || '\1'
                                      ),
                        '\d*(\d{127})',
                        '\1'
                       )

Now:

SQL> with t as (
  2             select '100' col1 from dual union all
  3             select '200' from dual union all
  4             select '100.2' from dual union all
  5             select '200.21' from dual union all
  6             select '200.1' from dual union all
  7             select '200.11' from dual union all
  8             select '200.2' from dual union all
  9             select '100.11' from dual union all
 10             select '100.1' from dual union all
 11             select '100.21' from dual
 12            )
 13  select  *
 14    from  t
 15  ORDER BY REGEXP_REPLACE(
 16                          REGEXP_REPLACE(
 17                                         COL1,
 18                                         '(\d+)',
 19                                         LPAD('0',127,'0') || '\1'
 20                                        ),
 21                          '\d*(\d{127})',
 22                          '\1'
 23                         )
 24  /

COL1
------
100
100.1
100.2
100.11
100.21
200
200.1
200.2
200.11
200.21

10 rows selected.

SQL>

This generic method left pads each set of digits with 127 zeroes and then takes 127 rightmost digits from each set thus aligning digit significance in each set.

SY.

Re: Format result set of a union [message #655639 is a reply to message #655637] Tue, 06 September 2016 18:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
Solomon,

That's still not quite right. For example, 100.11 needs to come before 100.2, not the other way around.
Re: Format result set of a union [message #655640 is a reply to message #655639] Tue, 06 September 2016 20:17 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
I see, OP doesn't want to order by number. Then I am completely confused since it is plain order by col1 (assuming all order numbers always have 3 digits):

SQL> with t as (
  2             select '100' col1 from dual union all
  3             select '200' from dual union all
  4             select '100.2' from dual union all
  5             select '200.21' from dual union all
  6             select '200.1' from dual union all
  7             select '200.11' from dual union all
  8             select '200.2' from dual union all
  9             select '100.11' from dual union all
 10             select '100.1' from dual union all
 11             select '100.21' from dual
 12            )
 13  select  *
 14    from  t
 15    order by col1
 16  /

COL1
------
100
100.1
100.11
100.2
100.21
200
200.1
200.11
200.2
200.21

10 rows selected.

SQL>

And whole thing implies order can't have more than 9 order lines and order line can't have more than 9 shipment detail lines. I think OP's query is flawed. COL1 should be:

100,200 are order numbers -- 100.1,100.2,200.1,200.2 are lines for those orders ---- 100.1.1,100.2.1,200.1.1,200.2.1 are shipment details for those order lines.

And, in general, we shouldn't be ordering by COL1. I assume OP has normalized relational design with tables ORDER, ORDER_LINE and ORDER_LINE_SHIPMENT_DETAIL. If so, OP's query is joining all three and therefore has columns ORDER_NUM, ORDER_LINE_NUM and ORDER_LINE_SHIPMENT_DETAIL_NUM. So OP can simply use ORDER BY ORDER_NUM, ORDER_LINE_NUM, ORDER_LINE_SHIPMENT_DETAIL_NUM.

SY.
Re: Format result set of a union [message #655671 is a reply to message #655640] Wed, 07 September 2016 03:56 Go to previous message
sandeep_orafaq
Messages: 88
Registered: September 2014
Member
Yes, In general there must be normalized DB modelling on these 3 tables. And also the sorting should not be done on the char or number values but it should be on relations of the entities.

From my experience with the order management database, I would assume that there are 3 tables with parent child hierarchy (ORDERS, ORDER_LINES, ORDER_LINE_SHIPMENTS) and they will be linked using at least 2 foreign keys present one in each child table. And the orderid , lines and shipment numbers will not be as expected as OP has mentioned in the example. PRoduction system can have as complex values as possible and hence any sorting done on values will be dangerous because it may not fit into all the possible values.

I have tried to explain the possible solution to this question with normalized relational model:



SQL> create table orders
  2  (orderid varchar2(100) primary key,
  3  col_desc1 varchar2(100),
  4  col_desc2 varchar2(100),
  5  col_desc3 varchar2(100),
  6  col_desc4 varchar2(100)
  7  );
Table created

SQL> 
SQL> create table order_lines
  2  (orderid varchar2(100)  not null,
  3  line_num varchar2(100) not null,
  4  col_desc1 varchar2(100),
  5  col_desc2 varchar2(100),
  6  col_desc3 varchar2(100),
  7  col_desc4 varchar2(100),
  8  constraint  fk_lines  foreign key(orderid)  references orders(orderid) on delete cascade,
  9  constraint uk_lines unique  (orderid,line_num)
 10  );
Table created

SQL> 
SQL> create table order_line_shipments
  2  (orderid varchar2(100) not null,
  3  line_num varchar2(100) not null,
  4  ship_num varchar2(100) not null,
  5  col_desc1 varchar2(100),
  6  col_desc2 varchar2(100),
  7  col_desc3 varchar2(100),
  8  col_desc4 varchar2(100),
  9  constraint  fk_ship1 foreign key(orderid,line_num)  references order_lines(orderid,line_num) on delete cascade
 10  );
Table created

SQL> 
SQL> 
SQL> --2 Orders
SQL> insert into orders (orderid,col_desc1,col_desc2,col_desc3,col_desc4) values ('100','Samsung','Newyork','US','50');
1 row inserted
SQL> insert into orders (orderid,col_desc1,col_desc2,col_desc3,col_desc4) values ('200','Sony','London','UK','60');
1 row inserted
SQL> -- 1st orders lines (2 Lines)
SQL> insert into order_lines (orderid,line_num,col_desc1,col_desc2,col_desc3,col_desc4) values ('100','10','TV','42 inches','LED','10');
1 row inserted
SQL> insert into order_lines (orderid,line_num,col_desc1,col_desc2,col_desc3,col_desc4) values ('100','20','Washing M/C','7 Kg','Top Load','20');
1 row inserted
SQL> -- 2nd orders lines (2 Lines)
SQL> insert into order_lines (orderid,line_num,col_desc1,col_desc2,col_desc3,col_desc4) values ('200','10','Headphones','30db','In-ear','50');
1 row inserted
SQL> insert into order_lines (orderid,line_num,col_desc1,col_desc2,col_desc3,col_desc4) values ('200','20','Speakers','500W','Dolby','10');
1 row inserted
SQL> --1st orders 1st lines shipments ( 2 shipments , assuming there are 2 schedules for shipments)
SQL> insert into order_line_shipments (orderid,line_num,ship_num,col_desc1,col_desc2,col_desc3,col_desc4) values ('100','10','1','Delivered','Ground','Fedex','1');
1 row inserted
SQL> insert into order_line_shipments (orderid,line_num,ship_num,col_desc1,col_desc2,col_desc3,col_desc4) values ('100','10','2','Delivered','Air','Fedex','2');
1 row inserted
SQL> --1st orders 2nd lines shipments ( 1 shipments)
SQL> insert into order_line_shipments (orderid,line_num,ship_num,col_desc1,col_desc2,col_desc3,col_desc4) values ('100','20','1','Delivered','Air','DHL','1');
1 row inserted
SQL> --2nd orders 1st lines shipments ( 1 shipments)
SQL> insert into order_line_shipments (orderid,line_num,ship_num,col_desc1,col_desc2,col_desc3,col_desc4) values ('200','10','1','In-Transit','Ground','Orient Exp','2');
1 row inserted
SQL> --2nd orders 2nd lines shipments ( 1 shipments)
SQL> set escape '\';
SQL> insert into order_line_shipments (orderid,line_num,ship_num,col_desc1,col_desc2,col_desc3,col_desc4) values ('200','20','1','Delivered','Air','AT\&T','1');
1 row inserted
SQL> commit;
Commit complete

SQL> 
SQL> 
SQL> 
SQL> select *
  2    from (select orderid   disp_column_column,
  3                 orderid,
  4                 null      line_num,
  5                 null      ship_num,
  6                 col_desc1,
  7                 col_desc2,
  8                 col_desc3,
  9                 col_desc4
 10            from orders
 11          union all
 12          select line_num  disp_column_column,
 13                 orderid,
 14                 line_num,
 15                 null      ship_num,
 16                 col_desc1,
 17                 col_desc2,
 18                 col_desc3,
 19                 col_desc4
 20            from order_lines
 21          union all
 22          select ship_num disp_column_column,
 23                 orderid,
 24                 line_num,
 25                 ship_num,
 26                 col_desc1,
 27                 col_desc2,
 28                 col_desc3,
 29                 col_desc4
 30            from order_line_shipments)
 31   order by orderid, line_num nulls first, ship_num nulls first;


DISP_COLUMN_COLUMN   ORDERID        LINE_NUM       SHIP_NUM    COL_DESC1     COL_DESC2    COL_DESC3       COL_DESC4
-------------------- ------------   -------------- ----------- ------------- -----------  --------------- ----------
100                  100                                        Samsung       Newyork      US              50
10                   100            10                          TV            42 inches    LED             10
1                    100            10              1           Delivered     Ground       Fedex           1
2                    100            10              2           Delivered     Air          Fedex           2
20                   100            20                          Washing M/C   7 Kg         Top Load        20
1                    100            20              1           Delivered     Air          DHL             1
200                  200                                        Sony          London       UK              60
10                   200            10                          Headphones    30db         In-ear          50
1                    200            10              1           In-Transit    Ground       Orient Exp      2
20                   200            20                          Speakers      500W         Dolby           10
1                    200            20              1           Delivered     Air          AT&T            1
11 rows selected

Previous Topic: Select columns based on first record (Header from flat file)
Next Topic: Unique Constraint With DEFERRABLE INITIALLY DEFERRED NOVALIDATE
Goto Forum:
  


Current Time: Thu Mar 28 03:20:47 CDT 2024