Home » SQL & PL/SQL » SQL & PL/SQL » Specifically inserting a Row into a Result (+_+)
icon5.gif  Specifically inserting a Row into a Result (+_+) [message #185686] Thu, 03 August 2006 02:10 Go to next message
freelanxer
Messages: 41
Registered: March 2005
Location: Philippines
Member
Considering the following data:
ITEM_NO	NAME	        ZONE	CHANGE_FLAG
1	Power Supply	1860	0
2	AVR		1860	0
3	Monitor		1860	0
1	Power Supply	1900	0
2	AVR		1900	0
3	Monitor		1900	0
1	Power Supply	2150	0
2	AVR		2150	0
3	Monitor		2150	0
6	Processor	2750	1



Arrow I want a result:
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


Arrow The concept is to select 1st the data with CHANGE_FLAG = 0
and ORDER BY ZONE, ITEM_NO

ITEM_NO	NAME		ZONE	CHANGE_FLAG
1	Power Supply	1860	0
2	AVR		1860	0
3	Monitor		1860	0
1	Power Supply	1900	0
2	AVR		1900	0
3	Monitor		1900	0
1	Power Supply	2150	0
2	AVR		2150	0
3	Monitor		2150	0

Idea Let's label this as RESULTA


Then selecting the second data with CHANGE_FLAG = 1.
(Note: There will be only one data in the table with CHANGE_FLAG = 1)

ITEM_NO	NAME		ZONE	CHANGE_FLAG
6	Processor	2750	1

Idea Let's label this as RESULTB


Arrow Combining the two result.
Everytime the ZONE changes in RESULTA, we will insert the result of RESULTB.

ITEM_NO	NAME		ZONE	CHANGE_FLAG
1	Power Supply	1860	0               <--- RESULTA
2	AVR		1860	0               <--- RESULTA
3	Monitor		1860	0               <--- RESULTA
6	Processor	2750	1               <<- RESULTB
1	Power Supply	1900	0               <--- RESULTA
2	AVR		1900	0               <--- RESULTA
3	Monitor		1900	0               <--- RESULTA
6	Processor	2750	1               <<- RESULTB
1	Power Supply	2150	0               <--- RESULTA
2	AVR		2150	0               <--- RESULTA
3	Monitor		2150	0               <--- RESULTA
6	Processor	2750	1               <<- RESULTB



I've tried to solve but I can't get my expected result.
Is there a way to solve this?
Please help. Dead
Re: Specifically inserting a Row into a Result (+_+) [message #185692 is a reply to message #185686] Thu, 03 August 2006 02:32 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #185706 is a reply to message #185692] Thu, 03 August 2006 03:36 Go to previous messageGo to next message
freelanxer
Messages: 41
Registered: March 2005
Location: Philippines
Member
Wow! It work! Thanks! Thumbs Up
But, a friend of mine consider a scenario wherein if there will be around 100,000 or more records, a performance problem could appear. Dead

Is there other other way of doing this? Or will CROSS join be ok? Question

Thank you very much Ross! Smile
Re: Specifically inserting a Row into a Result (+_+) [message #185863 is a reply to message #185706] Thu, 03 August 2006 21:07 Go to previous message
rleishman
Messages: 3724
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]



Previous Topic: Duplicate emails, but need to isolate column value
Next Topic: KUP-04063: unable to open log file EXTERNAL_AUTO1_TABLE_480_3988.log
Goto Forum:
  


Current Time: Sun Dec 04 16:43:22 CST 2016

Total time taken to generate the page: 0.35139 seconds