advanced select in Stored Procedure
- by Auro
Hey
i got this Table:
CREATE TABLE Test_Table (
old_val VARCHAR2(3),
new_val VARCHAR2(3),
Updflag NUMBER,
WorkNo NUMBER );
and this is in my Table:
INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('1',' 20',0,0);
INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('2',' 20',0,0);
INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('2',' 30',0,0);
INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('3',' 30',0,0);
INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('4',' 40',0,0);
INSERT INTO Test_Table (old_val, new_val, Updflag , WorkNo) VALUES('4',' 40',0,0);
now my Table Looks like this:
Row Old_val New_val Updflag WorkNo
1 '1' ' 20' 0 0
2 '2' ' 20' 0 0
3 '2' ' 30' 0 0
4 '3' ' 30' 0 0
5 '4' ' 40' 0 0
6 '5' ' 40' 0 0
(if the value in the new_val column are same then they are together and the same goes to old_val)
so in the example above row 1-4 are together and row 5-6
at the moment i have in my Stored Procedure a cursor:
SELECT t1.Old_val, t1.New_val, t1.updflag, t1.WorkNo
FROM Test_Table t1
WHERE t1.New_val =
(
SELECT t2.New_val
FROM Test_Table t2
WHERE t2.Updflag = 0
AND t2.Worknr = 0
AND ROWNUM = 1
)
the output is this:
Row Old_val New_val Updflag WorkNo
1 1 20 0 0
2 2 20 0 0
my Problem is, i dont know how to get row 1 to 4 with one select.
(i had an idea with 4 sub-querys but this wont work if its more data that matches together)
does anyone of you have an idea?
