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 |
|
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 #655350 is a reply to message #655346] |
Mon, 29 August 2016 08:35 |
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 #655357 is a reply to message #655356] |
Mon, 29 August 2016 08:46 |
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 #655364 is a reply to message #655361] |
Mon, 29 August 2016 13:29 |
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 |
|
Barbara Boehmer
Messages: 9095 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 #655627 is a reply to message #655373] |
Tue, 06 September 2016 08:12 |
Solomon Yakobson
Messages: 3273 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 #655634 is a reply to message #655627] |
Tue, 06 September 2016 15:38 |
|
Barbara Boehmer
Messages: 9095 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 |
|
Barbara Boehmer
Messages: 9095 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
sandeep_orafaq wrote on Tue, 06 September 2016 04:11This 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 |
Solomon Yakobson
Messages: 3273 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 #655640 is a reply to message #655639] |
Tue, 06 September 2016 20:17 |
Solomon Yakobson
Messages: 3273 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 |
|
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
|
|
|
Goto Forum:
Current Time: Sun May 19 10:18:51 CDT 2024
|