8 Replies Latest reply: Apr 2, 2013 8:01 AM by pollywog RSS

    generate column value based on conditions

    pollywog
      BANNER
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      CORE     11.2.0.3.0     Production
      TNS for HPUX: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
      select statement to generate input
      WITH t
           AS (    SELECT 1 job_request_id,
                          1 original_job_request_id,
                          mod(level,3) +1  sequence_cd,
                          CHR (LEVEL + 100) value_txt,
                          NULL new_sequence_cd
                     FROM DUAL
               CONNECT BY LEVEL < 6),
           t1
           AS (    SELECT 2 job_request_id,
                          1 original_job_request_id,
                          mod(LEVEL,2) + 1 sequence_cd,
                          CHR (LEVEL + 110) value_txt,
                          NULL
                     FROM DUAL
               CONNECT BY LEVEL < 10),
           t2
           AS (    SELECT 3 job_request_id,
                          3 original_job_request_id,
                          mod(LEVEL,3) + 7 sequence_cd,
                          CHR (LEVEL + 95) value_txt,
                          NULL
                     FROM DUAL
               CONNECT BY LEVEL < 9),
           t3
           AS (    SELECT 4 job_request_id,
                          3 original_job_request_id,
                          mod(LEVEL,2) + 1 sequence_cd,
                          CHR (LEVEL + 95) value_txt,
                          NULL
                     FROM DUAL
               CONNECT BY LEVEL < 7),
           t4
           AS (    SELECT 7 job_request_id,
                          3 original_job_request_id,
                          mod(LEVEL,2) + 1 sequence_cd,
                          CHR (LEVEL + 92) value_txt,
                          NULL
                     FROM DUAL
               CONNECT BY LEVEL < 4),
               
           mytable
           AS (SELECT * FROM t
               UNION ALL
               SELECT * FROM t1
               UNION ALL
               SELECT * FROM t2
               UNION ALL
               SELECT * FROM t3
               UNION ALL
               SELECT * FROM t4)
      SELECT *
        FROM mytable
        order by job_request_id, original_job_request_id, sequence_cd;
      input
      JOB_REQUEST_ID     ORIGINAL_JOB_REQUEST_ID     SEQUENCE_CD     VALUE_TXT     NEW_SEQUENCE_CD
      1     1     1     g     
      1     1     2     h     
      1     1     2     e     
      1     1     3     i     
      1     1     3     f     
      2     1     1     v     
      2     1     1     p     
      2     1     1     r     
      2     1     1     t     
      2     1     2     u     
      2     1     2     q     
      2     1     2     o     
      2     1     2     s     
      2     1     2     w     
      3     3     7     b     
      3     3     7     e     
      3     3     8     `     
      3     3     8     f     
      3     3     8     c     
      3     3     9     d     
      3     3     9     a     
      3     3     9     g     
      4     3     1     e     
      4     3     1     c     
      4     3     1     a     
      4     3     2     b     
      4     3     2     `     
      4     3     2     d     
      7     3     1     ^     
      7     3     2     ]     
      7     3     2     _     
      expected output
      JOB_REQUEST_ID     ORIGINAL_JOB_REQUEST_ID     SEQUENCE_CD     VALUE_TXT     NEW_SEQUENCE_CD
      1     1     1     g     1
      1     1     2     h     2
      1     1     2     e     2
      1     1     3     i     3
      1     1     3     f     3
      2     1     1     v     4
      2     1     1     p     4
      2     1     1     r     4
      2     1     1     t     4
      2     1     2     u     5
      2     1     2     q     5
      2     1     2     o     5
      2     1     2     s     5
      2     1     2     w     5
      3     3     7     b     7
      3     3     7     e     7
      3     3     8     `     8
      3     3     8     f     8
      3     3     8     c     8
      3     3     9     d     9
      3     3     9     a     9
      3     3     9     g     9
      4     3     1     e     10
      4     3     1     c     10
      4     3     1     a     10
      4     3     2     b     11
      4     3     2     `     11
      4     3     2     d     11
      7     3     1     ^     12
      7     3     2     ]     13
      7     3     2     _     13
      my attempt to explain.
      if the job request id = the original job request id the new sequence cd = sequence cd

      when the job request ids are higher than the original increment the new sequence cd with respect to the maximim sequence cd of the original job request id.

      hopefully the expected output will clarify.
        • 1. Re: generate column value based on conditions
          Hoek
          Not sure why you leave out 6 in your desired resultset, but have you fiddled with DENSE_RANK already?
          Try:
          WITH t
               AS (    SELECT 1 job_request_id,
                              1 original_job_request_id,
                              mod(level,3) +1  sequence_cd,
                              CHR (LEVEL + 100) value_txt,
                              NULL new_sequence_cd
                         FROM DUAL
                   CONNECT BY LEVEL < 6),
               t1
               AS (    SELECT 2 job_request_id,
                              1 original_job_request_id,
                              mod(LEVEL,2) + 1 sequence_cd,
                              CHR (LEVEL + 110) value_txt,
                              NULL
                         FROM DUAL
                   CONNECT BY LEVEL < 10),
               t2
               AS (    SELECT 3 job_request_id,
                              3 original_job_request_id,
                              mod(LEVEL,3) + 7 sequence_cd,
                              CHR (LEVEL + 95) value_txt,
                              NULL
                         FROM DUAL
                   CONNECT BY LEVEL < 9),
               t3
               AS (    SELECT 4 job_request_id,
                              3 original_job_request_id,
                              mod(LEVEL,2) + 1 sequence_cd,
                              CHR (LEVEL + 95) value_txt,
                              NULL
                         FROM DUAL
                   CONNECT BY LEVEL < 7),
               t4
               AS (    SELECT 7 job_request_id,
                              3 original_job_request_id,
                              mod(LEVEL,2) + 1 sequence_cd,
                              CHR (LEVEL + 92) value_txt,
                              NULL
                         FROM DUAL
                   CONNECT BY LEVEL < 4),
          
               mytable
               AS (SELECT * FROM t
                   UNION ALL
                   SELECT * FROM t1
                   UNION ALL
                   SELECT * FROM t2
                   UNION ALL
                   SELECT * FROM t3
                   UNION ALL
                   SELECT * FROM t4
          )
          --
          --
          --
          select job_request_id
          ,      original_job_request_id
          ,      sequence_cd
          ,      value_txt
          ,      dense_rank() over (order by original_job_request_id
                                   ,         job_request_id
                                    ,        sequence_cd
                                   ) new_sequence_cd
          from   mytable
          order by job_request_id
          ,        original_job_request_id
          ,        sequence_cd
          • 2. Re: generate column value based on conditions
            Ramin Hashimzadeh
            this?
            WITH t
                 AS (    SELECT 1 job_request_id,
                                1 original_job_request_id,
                                mod(level,3) +1  sequence_cd,
                                CHR (LEVEL + 100) value_txt,
                                NULL new_sequence_cd
                           FROM DUAL
                     CONNECT BY LEVEL < 6),
                 t1
                 AS (    SELECT 2 job_request_id,
                                1 original_job_request_id,
                                mod(LEVEL,2) + 1 sequence_cd,
                                CHR (LEVEL + 110) value_txt,
                                NULL
                           FROM DUAL
                     CONNECT BY LEVEL < 10),
                 t2
                 AS (    SELECT 3 job_request_id,
                                3 original_job_request_id,
                                mod(LEVEL,3) + 7 sequence_cd,
                                CHR (LEVEL + 95) value_txt,
                                NULL
                           FROM DUAL
                     CONNECT BY LEVEL < 9),
                 t3
                 AS (    SELECT 4 job_request_id,
                                3 original_job_request_id,
                                mod(LEVEL,2) + 1 sequence_cd,
                                CHR (LEVEL + 95) value_txt,
                                NULL
                           FROM DUAL
                     CONNECT BY LEVEL < 7),
                 t4
                 AS (    SELECT 7 job_request_id,
                                3 original_job_request_id,
                                mod(LEVEL,2) + 1 sequence_cd,
                                CHR (LEVEL + 92) value_txt,
                                NULL
                           FROM DUAL
                     CONNECT BY LEVEL < 4),
                     
                 mytable
                 AS (SELECT * FROM t
                     UNION ALL
                     SELECT * FROM t1
                     UNION ALL
                     SELECT * FROM t2
                     UNION ALL
                     SELECT * FROM t3
                     UNION ALL
                     SELECT * FROM t4)
            SELECT job_request_id,
                   original_job_request_id,
                   sequence_cd,
                   value_txt,
                   nvl(new_sequence_cd,LAG(new_sequence_cd IGNORE NULLS) OVER (ORDER BY job_request_id, original_job_request_id, sequence_cd)+sequence_cd) new_sequence_cd
            FROM(
            SELECT job_request_id,
                   original_job_request_id,
                   sequence_cd,
                   value_txt,
                   DECODE(job_request_id,
                          original_job_request_id,
                          sequence_cd,NULL)        new_sequence_cd
              FROM mytable
              order by job_request_id, original_job_request_id, sequence_cd
              )
            
            
            JOB_REQUEST_ID     ORIGINAL_JOB_REQUEST_ID     SEQUENCE_CD     VALUE_TXT     NEW_SEQUENCE_CD
            1     1     1     g     1
            1     1     2     h     2
            1     1     2     e     2
            1     1     3     i     3
            1     1     3     f     3
            2     1     1     v     4
            2     1     1     p     4
            2     1     1     r     4
            2     1     1     t     4
            2     1     2     u     5
            2     1     2     q     5
            2     1     2     o     5
            2     1     2     s     5
            2     1     2     w     5
            3     3     7     b     7
            3     3     7     e     7
            3     3     8     `     8
            3     3     8     f     8
            3     3     8     c     8
            3     3     9     d     9
            3     3     9     a     9
            3     3     9     g     9
            4     3     1     e     10
            4     3     1     c     10
            4     3     1     a     10
            4     3     2     b     11
            4     3     2     `     11
            4     3     2     d     11
            7     3     1     ^     10
            7     3     2     ]     11
            7     3     2     _     11
            ----
            Ramin Hashimzadeh
            • 3. Re: generate column value based on conditions
              Frank Kulash
              Hi,

              It looks like you want DENSE_RANK, except that you want to skip numbers (e.g., 6 in this example) when necessary so that new_sequence_cd >= sequence_cd.

              Using analytic functions:
              WITH      got_drank     AS
              (
                   SELECT    m.*
                   ,       DENSE_RANK () OVER ( ORDER BY  job_request_id
                                                      ,      original_job_request_id
                                                ,      sequence_cd
                                            )  AS drank
                   FROM      mytable  m
              )
              SELECT       job_request_id
              ,            original_job_request_id
              ,        sequence_cd
              ,       value_txt
              ,       drank + MAX ( sequence_cd 
                                - drank
                              ) OVER ( ORDER BY  job_request_id
                                          ,      original_job_request_id
                                    ,      sequence_cd
                                  )  AS new_sequence_cd
              FROM      got_drank
              ORDER BY  job_request_id
              ,            original_job_request_id
              ,        sequence_cd
              ;
              You could also use MODEL.

              If you have a new_sequence_cd column in a table, an you want to populate it, use the query above in a MERGE statement.
              • 4. Re: generate column value based on conditions
                pollywog
                job request 7 appears to be incorrect.
                • 5. Re: generate column value based on conditions
                  pollywog
                  6 is left out because job request 3 = original job request so now updates done there.
                  • 6. Re: generate column value based on conditions
                    Ramin Hashimzadeh
                    WITH t
                         AS (    SELECT 1 job_request_id,
                                        1 original_job_request_id,
                                        mod(level,3) +1  sequence_cd,
                                        CHR (LEVEL + 100) value_txt,
                                        NULL new_sequence_cd
                                   FROM DUAL
                             CONNECT BY LEVEL < 6),
                         t1
                         AS (    SELECT 2 job_request_id,
                                        1 original_job_request_id,
                                        mod(LEVEL,2) + 1 sequence_cd,
                                        CHR (LEVEL + 110) value_txt,
                                        NULL
                                   FROM DUAL
                             CONNECT BY LEVEL < 10),
                         t2
                         AS (    SELECT 3 job_request_id,
                                        3 original_job_request_id,
                                        mod(LEVEL,3) + 7 sequence_cd,
                                        CHR (LEVEL + 95) value_txt,
                                        NULL
                                   FROM DUAL
                             CONNECT BY LEVEL < 9),
                         t3
                         AS (    SELECT 4 job_request_id,
                                        3 original_job_request_id,
                                        mod(LEVEL,2) + 1 sequence_cd,
                                        CHR (LEVEL + 95) value_txt,
                                        NULL
                                   FROM DUAL
                             CONNECT BY LEVEL < 7),
                         t4
                         AS (    SELECT 7 job_request_id,
                                        3 original_job_request_id,
                                        mod(LEVEL,2) + 1 sequence_cd,
                                        CHR (LEVEL + 92) value_txt,
                                        NULL
                                   FROM DUAL
                             CONNECT BY LEVEL < 4),
                             
                         mytable
                         AS (SELECT * FROM t
                             UNION ALL
                             SELECT * FROM t1
                             UNION ALL
                             SELECT * FROM t2
                             UNION ALL
                             SELECT * FROM t3
                             UNION ALL
                             SELECT * FROM t4)
                    SELECT job_request_id,
                           original_job_request_id,
                           sequence_cd,
                           value_txt,
                           nvl(new_sequence_cd,DENSE_RANK () OVER ( ORDER BY  job_request_id,original_job_request_id, sequence_cd)) new_sequence_cd
                           
                    FROM(
                    SELECT job_request_id,
                           original_job_request_id,
                           sequence_cd,
                           value_txt,
                           DECODE(job_request_id,
                                  original_job_request_id,
                                  sequence_cd,NULL)        new_sequence_cd
                             
                      FROM mytable
                      order by job_request_id, original_job_request_id, sequence_cd
                      )
                    
                    JOB_REQUEST_ID     ORIGINAL_JOB_REQUEST_ID     SEQUENCE_CD     VALUE_TXT     NEW_SEQUENCE_CD
                    1     1     1     g     1
                    1     1     2     h     2
                    1     1     2     e     2
                    1     1     3     i     3
                    1     1     3     f     3
                    2     1     1     v     4
                    2     1     1     p     4
                    2     1     1     r     4
                    2     1     1     t     4
                    2     1     2     u     5
                    2     1     2     q     5
                    2     1     2     o     5
                    2     1     2     s     5
                    2     1     2     w     5
                    3     3     7     b     7
                    3     3     7     e     7
                    3     3     8     `     8
                    3     3     8     f     8
                    3     3     8     c     8
                    3     3     9     d     9
                    3     3     9     a     9
                    3     3     9     g     9
                    4     3     1     e     9
                    4     3     1     c     9
                    4     3     1     a     9
                    4     3     2     b     10
                    4     3     2     `     10
                    4     3     2     d     10
                    7     3     1     ^     11
                    7     3     2     ]     12
                    7     3     2     _     12
                    • 7. Re: generate column value based on conditions
                      Hoek
                      Ah, missed that one, got it, and I believe Frank got it as well ;)