StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POadvanced select in Stored Procedure
text
Body
copied!<p>i got this Table:</p> <pre><code>CREATE TABLE Test_Table ( old_val VARCHAR2(3), new_val VARCHAR2(3), Updflag NUMBER, WorkNo NUMBER ); </code></pre> <p>and this is in my Table:</p> <pre><code>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); </code></pre> <p>now my Table Looks like this:</p> <pre><code>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 </code></pre> <p>(if the value in the <code>new_val</code> column are same then they are together and the same goes to <code>old_val</code>) so in the example above row 1-4 are together and row 5-6</p> <p>at the moment i have in my Stored Procedure a cursor:</p> <pre><code> 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 ) </code></pre> <p>the output is this:</p> <pre><code>Row Old_val New_val Updflag WorkNo 1 1 20 0 0 2 2 20 0 0 </code></pre> <p>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)</p> <p>does anyone of you have an idea?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload