advanced select in Stored Procedure

Question

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?

Answer 1

Hi Auro,

you can use analytics to define groups of contiguous rows:

SQL> SELECT old_val, new_val, updflag, workno,
  2         SUM(gap) over(ORDER BY old_val, new_val) grp
  3    FROM (SELECT t.*,
  4                  CASE
  5                     WHEN new_val = lag(new_val)
  6                                    over(ORDER BY old_val, new_val)
  7                       OR old_val = lag(old_val)
  8                                    over(ORDER BY old_val, new_val)
  9                     THEN
 10                      0
 11                     ELSE
 12                      1
 13                  END gap
 14             FROM Test_Table t);

OLD_VAL NEW_VAL    UPDFLAG     WORKNO        GRP
------- ------- ---------- ---------- ----------
1        20              0          0          1
2        20              0          0          1
2        30              0          0          1
3        30              0          0          1
4        40              0          0          2
4        40              0          0          2

The inner SELECT builds a "GAP" column that equals 1 when the current row is not in the same group as the preceeding.

The outer SELECT uses a running total over the gap column to get the group number.

Edit 2

You can't add the FOR UPDATE clause directly to the query because of the analytic functions. You can query the base table directly however:

SQL> WITH t_new AS (
  2  SELECT t_rowid, old_val, new_val, updflag, workno,
  3         SUM(gap) over(ORDER BY old_val, new_val) grp
  4    FROM (SELECT t.*, t.rowid t_rowid,
  5                  CASE
  6                     WHEN new_val = lag(new_val)
  7                                    over(ORDER BY old_val, new_val)
  8                       OR old_val = lag(old_val)
  9                                    over(ORDER BY old_val, new_val)
 10                     THEN
 11                      0
 12                     ELSE
 13                      1
 14                  END gap
 15             FROM test_table t)
 16  )
 17  SELECT *
 18    FROM test_table
 19   WHERE ROWID IN (SELECT t_rowid
 20                     FROM t_new
 21                    WHERE grp = (SELECT grp
 22                                   FROM t_new t2
 23                                  WHERE t2.new_val = ' 20'
 24                                    AND t2.old_val = '1'))
 25     FOR UPDATE;

OLD_VAL NEW_VAL    UPDFLAG     WORKNO
------- ------- ---------- ----------
1        20              0          0
2        20              0          0
2        30              0          0
3        30              0          0

Answer 2

If what you want is all rows that "are together with something" returned, then it does not appear that your original sample data provides a row that shouldn't be returned. So, let's add the following:

INSERT INTO Test_Table  (old_val, new_val, Updflag , WorkNo) VALUES('6',' 50',0,0);

This row shouldn't be together with anything and shouldn't be returned. Given that, I think we can use an EXISTS to get what you want:

Select *
From Test_Table T1
Where Exists    (
                Select 1
                From Test_Table T2
                Where ( T2.old_val = T1.old_val Or T2.new_val = T1.new_val )
                    And ( T2.row <> T1.row )
                )