CODEWITHSUNDEEP
sqldatabaseoracle-databaseplsqloracle11g
Srini V
Srini V

Reputation: 11355

How to increment an oracle sequence after a set of rows or conditionally without PL/SQL block?

I am trying to perform code rewrite. One among them was this monster. I am having a set of normal DML's and all of a sudden I have this PL/SQL Block inside a script (see the current solution in place) which looks odd among SQL DML's.

Initially we decided to go for PL/SQL block on the assumption that, "updating a column with a sequence number without increment within the group of records and incremented for the next group CANNOT BE achieved in a single SQL."

row1 - seq.nextval
row2 - seq.currval
row3 - seq.nextval
row4 - seq.currval
row5 - seq.currval

where group1 {row1, row2} and group 2 {row3, row4, row5}

Question: How to increment an oracle sequence after a set of rows or conditionally?

Data Setup:

CREATE TABLE TEMP_GP_SEQ
(
   COL1   NUMBER,
   COL2   NUMBER,
   COL3   NUMBER,
   COL4   NUMBER,
   COL5   NUMBER,
   COL6   NUMBER,
   COL7   VARCHAR2 (10)
);

INSERT INTO TEMP_GP_SEQ VALUES(1,10,100,NULL,NULL,NULL,NULL);
INSERT INTO TEMP_GP_SEQ VALUES(1,10,101,NULL,NULL,NULL,NULL);
INSERT INTO TEMP_GP_SEQ VALUES(12,10,100,1,NULL,NULL,NULL);
INSERT INTO TEMP_GP_SEQ VALUES(1,10,100,2,NULL,NULL,NULL);
INSERT INTO TEMP_GP_SEQ VALUES(1,10,100,3,NULL,NULL,NULL);
INSERT INTO TEMP_GP_SEQ VALUES(12,10,100,1,NULL,NULL,NULL);
INSERT INTO TEMP_GP_SEQ VALUES(2,10,100,NULL,NULL,NULL,NULL);
INSERT INTO TEMP_GP_SEQ VALUES(2,10,101,NULL,NULL,NULL,NULL);
INSERT INTO TEMP_GP_SEQ VALUES(2,10,101,1,NULL,NULL,NULL);
INSERT INTO TEMP_GP_SEQ VALUES(2,10,101,1,NULL,NULL,NULL);

COMMIT; 

CREATE SEQUENCE SEQ_TEMP_TEST
   START WITH 1
   INCREMENT BY 1;

So selection criterion for my concerned records

SELECT
      COL1,
      COL3,
      COL4,
      COUNT ( * )
FROM
      TEMP_GP_SEQ
GROUP BY
      COL1,
      COL3,
      COL4
HAVING
      COUNT ( * ) > 1;

Would give me

COL1    COL3    COL4    COUNT(*)
2       101     1       2
12      100     1       2

I need to update the TEMP_GP_SEQ table and on the COLUMN COL7 based on the grouping range mentioned above using the sequence created. But the sequence should not be incremented for each and every record, it should be incremented only for change of groups. For example

SELECT 
      COL1,
      COL2,
      COL3,
      COL4,
      COL7
FROM
      TEMP_GP_SEQ;

Desired Output

COL1    COL2    COL3   COL4    COL7         
1       10      100    NULL    NULL         
1       10      101    NULL    NULL         
12      10      100    1       M2           
1       10      100    2       NULL         
1       10      100    3       NULL         
12      10      100    1       M2           
2       10      100    NULL    NULL         
2       10      101    NULL    NULL         
2       10      101    1       M1           
2       10      101    1       M1

COL7 is updated for these four rows with M as a prefixed word and the number followed after M is from the sequence. Here the number is changing (sequence is incremented) only when there is a change in grouping criteria and it remains the same throughout a group.

Challenge is that there is a possibility of NULL values in any of the columns. So while grouping the NULLS should be considered. Hence IS NULL is used. (NVL is ignored just to be sure)

Current solution in place:

DECLARE
    VAL INTEGER;
BEGIN
    FOR REC IN ( SELECT
                    COL1,
                    COL3,
                    COL4
              FROM
                    TEMP_GP_SEQ
              GROUP BY
                    COL1,
                    COL3,
                    COL4
              HAVING
                    COUNT ( * ) > 1 )
    LOOP
        SELECT SEQ_TEMP_TEST.NEXTVAL INTO VAL FROM DUAL;

        UPDATE
              TEMP_GP_SEQ
        SET
              COL7   = 'M' || VAL
        WHERE
                 ( COL1 = REC.COL1 OR ( COL1 IS NULL AND REC.COL1 IS NULL ) )
              AND ( COL3 = REC.COL3 OR ( COL3 IS NULL AND REC.COL3 IS NULL ) )
              AND ( COL4 = REC.COL4 OR ( COL4 IS NULL AND REC.COL4 IS NULL ) );
    END LOOP;
END;
/

Is it really possible to refactor this to normal SQL instead of a PL/SQL block? If you need any clarifications let me know.

Anyone want to try can see the fiddle here

Upvotes: 2

Views: 6638

Answers (1)

Jorge Campos
Jorge Campos

Reputation: 23361

If I understand your problem right this query would do what you want then you just need use it to update your rows. But I didn't use the update to change the rows nor use fiddle because (I don't know why) it won't let me create a function.

In order to put my select statement to run I need to create a function just to return the sequence because oracle won't let me use it inside my sql statement (at least not on my version here 10.2.x).

So first of all I create this function:

create or replace function retSeq return number
as
   n number;
begin
   select SEQ_TEMP_TEST.nextval into n from dual;
   return n;
end;

Then I did the select statement. I think that change your code to this would turn it much more difficult to understand. But the question is to solve the problem with one query, I almost did it (had to create the function). So, don't be scary:

SELECT s1.col1, s1.col2, s1.col3, s1.col4, s1.col5, s1.col6, 
       decode(s1.id,null,'','M')
           || (SELECT retseq seq
                 FROM (SELECT   col1, col3, col4,
                                ROW_NUMBER () OVER (ORDER BY col1, col3, col4) ID
                         FROM temp_gp_seq
                        GROUP BY col1, col3, col4
                       HAVING COUNT (*) > 1)
                WHERE ID = s1.ID)
           as col7 
  FROM (SELECT a.*, b.ID
          FROM temp_gp_seq a,
               (SELECT col1, col3, col4,
                       ROW_NUMBER () OVER (ORDER BY col1, col3, col4) ID
                  FROM (SELECT   col1, col3, col4, COUNT (*) ct
                            FROM temp_gp_seq
                        GROUP BY col1, col3, col4
                          HAVING COUNT (*) > 1)) b
         WHERE a.col1 = b.col1(+) 
           AND a.col3 = b.col3(+)
           AND a.col4 = b.col4(+)) s1

The result will be (on the first run, because of the sequence)

COL1     COL2     COL3     COL4     COL5     COL6     COL7
 2        10       101      1                          M1
 2        10       101      1                          M1
 12       10       100      1                          M2
 12       10       100      1                          M2
 1        10       100      3                          
 1        10       100      2                          
 1        10       100                                
 2        10       101                                
 1        10       101                                
 2        10       100

FOLLOW UP BY OP:

SELECT
      A.COL1,
      A.COL2,
      A.COL3,
      A.COL4,
      A.COL5,
      A.COL6,
      DECODE ( B.ID, NULL, '', 'M' )
      || SEQ
          AS COL7
FROM
      TEMP_GP_SEQ A,
      (SELECT
            COL1,
            COL3,
            COL4,
            RETSEQ SEQ,
            ROW_NUMBER ( )
                OVER ( ORDER BY
                          COL1,
                          COL3,
                          COL4 )
                ID
       FROM
            TEMP_GP_SEQ
       GROUP BY
            COL1,
            COL3,
            COL4
       HAVING
            COUNT ( * ) > 1) B
WHERE
         A.COL1 = B.COL1(+)
      AND A.COL3 = B.COL3(+)
      AND A.COL4 = B.COL4(+);

PS: Removed the unnecessary sub queries and clubbed the window functions in one.

Upvotes: 2

Related Questions