Home » SQL & PL/SQL » SQL & PL/SQL » Specifically inserting a Row into a Result (+_+)
|
Re: Specifically inserting a Row into a Result (+_+) [message #185692 is a reply to message #185686] |
Thu, 03 August 2006 02:32   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
CREATE TABLE rl_test (
item_no NUMBER
,NAME VARCHAR2(30)
,ZONE VARCHAR2(4)
,change_flag NUMBER(1)
);
INSERT INTO rl_test
VALUES
(1,'Power Supply ','1860',0)
/
INSERT INTO rl_test
VALUES
(2,'AVR','1860',0)
/
INSERT INTO rl_test
VALUES
(3,'Monitor','1860',0)
/
INSERT INTO rl_test
VALUES
(1,'Power Supply ','1900',0)
/
INSERT INTO rl_test
VALUES
(2,'AVR','1900',0)
/
INSERT INTO rl_test
VALUES
(3,'Monitor','1900',0)
/
INSERT INTO rl_test
VALUES
(1,'Power Supply ','2150',0)
/
INSERT INTO rl_test
VALUES
(2,'AVR','2150',0)
/
INSERT INTO rl_test
VALUES
(3,'Monitor','2150',0)
/
INSERT INTO rl_test
VALUES
(6,'Processor','2750',1)
/
SELECT a.zone, a.*
FROM rl_test a
WHERE change_flag = 0
UNION ALL
SELECT b.zone, a.*
FROM rl_test a
CROSS JOIN (
SELECT DISTINCT ZONE
FROM rl_test
WHERE change_flag = 0) b
WHERE change_flag = 1
ORDER BY 1, 2
/
ITEM_NO NAME ZONE CHANGE_FLAG
---------- ------------------------------ ---- -----------
1 Power Supply 1860 0
2 AVR 1860 0
3 Monitor 1860 0
6 Processor 2750 1
1 Power Supply 1900 0
2 AVR 1900 0
3 Monitor 1900 0
6 Processor 2750 1
1 Power Supply 2150 0
2 AVR 2150 0
3 Monitor 2150 0
6 Processor 2750 1
Ross Leishman
|
|
|
|
Re: Specifically inserting a Row into a Result (+_+) [message #185863 is a reply to message #185706] |
Thu, 03 August 2006 21:07  |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
It shouldn't be a problem. The query does three FTS of the table, but the rest is pretty efficient. The CROSS JOIN is not a problem - it just duplicates a single row N times.
If you index the CHANGE_FLAG, it will only do two FTS.
Ross Leishman]
|
|
|
Goto Forum:
Current Time: Sat Feb 15 02:27:25 CST 2025
|