Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2008
    Posts
    94

    Unanswered: Update query for cdc?

    Code:
    drop table temppp;
    create table temppp(uniqueid integer,employeeid varchar(15),skillid varchar(10),sname varchar(90),prof varchar2(10),acode varchar(1),
    adesc varchar(30),start_date date,end_date date,rind varchar(1));
    
    insert into temppp values(3,10,'1','Unix','Basic','1','ABC',to_date(SYSDATE-1,'YYYY-MM-DD'),
    to_date(SYSDATE-1,'YYYY-MM-DD'),'N');
    
    insert into temppp values(4,10,'1','Unix Shell','Basic','2','XYZ',to_date(SYSDATE,'YYYY-MM-DD'),
    to_date(NULL,'YYYY-MM-DD'),'Y');
    
    insert into temppp values(5,10,'2','Pro*C','Basic','1','ABC',to_date(SYSDATE-1,'YYYY-MM-DD'),
    to_date(SYSDATE-1,'YYYY-MM-DD'),'N');
    
    insert into temppp values(5,10,'2','Pro*C','Expert','2','XYZ',to_date(SYSDATE,'YYYY-MM-DD'),
    to_date(NULL,'YYYY-MM-DD'),'Y');
    
    insert into temppp values(8,10,'5','Perl','Expert','1','XYZ',to_date(SYSDATE,'YYYY-MM-DD'),
    to_date(NULL,'YYYY-MM-DD'),'Y');
    
    insert into temppp values(1,10,'3','Java','Basic','1','ABC',to_date(SYSDATE-1,'YYYY-MM-DD'),
    to_date(SYSDATE-1,'YYYY-MM-DD'),'N');
    
    insert into temppp values(2,10,'3','Sun Java','Basic','2','XYZ',to_date(SYSDATE,'YYYY-MM-DD'),
    to_date(NULL,'YYYY-MM-DD'),'Y');
    
    insert into temppp values(3,11,'3','Java','Basic','1','ABC',to_date(SYSDATE-1,'YYYY-MM-DD'),
    to_date(SYSDATE-1,'YYYY-MM-DD'),'N');
    
    insert into temppp values(4,11,'3','Sun Java','Basic','2','XYZ',to_date(SYSDATE,'YYYY-MM-DD'),
    to_date(NULL,'YYYY-MM-DD'),'Y');
    
    
    insert into temppp values(1,11,'1','Unix','Basic','1','ABC',to_date(SYSDATE-1,'YYYY-MM-DD'),
    to_date(SYSDATE-1,'YYYY-MM-DD'),'N');
    
    insert into temppp values(2,11,'1','A Unix','Basic','2','XYZ',to_date(SYSDATE,'YYYY-MM-DD'),
    to_date(NULL,'YYYY-MM-DD'),'Y');
    
    
    Update temppp g 
    set g.acode='6',
    g.adesc='M XYZ'
    where 
    (g.employeeid,g.uniqueid) in
    (Select f.employeeid,f.uniqueid 
    from
    (select e.employeeid,e.uniqueid,
    rank() over(partition by e.employeeid,e.skillid order by e.uniqueid) as M_change,
    rank() over(partition by e.employeeid,e.skillid order by e.sname,e.prof) as B_change
    from
    (select a.* 
    from temppp a where
    a.end_date=to_date(sysdate-1,'yyyy-mm-dd')
    and a.rind='N'
    union all
    select c.* 
    from temppp c Where
    c.start_date=to_date(sysdate,'yyyy-mm-dd')
    and c.acode='2' 
    and c.rind='Y') e 
    ) f where f.M_change=2 
    and f.B_change=1 ) and g.acode='2' and g.rind='Y'
    1. When Skill Name and Unique ID changes then no need to update
    2. When Skill Name Changes and Unique ID is same then no need to update
    3. Only if Unique ID Changes then Update ACODE=6 and ADESC= M XYZ Where END_DATE =sysdate-1 and ACODE=2;

    objective is to compare the previous row with current row and update if only UNIQUE ID changes.

    so far i have written the above query. but it is wrongly updating. please advise.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Please supply more information including basic information such as DB2 version/release, so on...
    http://www.dbforums.com/db2/854783-m...e-posting.html

    You didn't lean from your experince.
    I wrote similar things by referencing above link in this thread.
    http://www.dbforums.com/db2/1671168-...nal-joins.html

  3. #3
    Join Date
    Jul 2008
    Posts
    94
    sorry tonkuma.

    DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL09014" with
    level identifier "01050107".
    Informational tokens are "DB2 v9.1.400.359", "s071028", "WR21396", and Fix Pack

    "4".

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I got error message by your create table statement.
    Code:
    ------------------------------ Commands Entered ------------------------------
    create table temppp(uniqueid integer,employeeid varchar(15),skillid varchar(10),sname varchar(90),prof varchar2(10),acode varchar(1),
    adesc varchar(30),start_date date,end_date date,rind varchar(1));
    ------------------------------------------------------------------------------
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0104N  An unexpected token "varchar2" was found following "ME varchar(90),
    PROF ".  Expected tokens may include:  "VARCHAR".  SQLSTATE=42601
    Please copy and paste exactly what you did.


    And, please write your result of "SELECT * FROM temppp".
    (1) Before execution of your update statement.
    (2) After execution of your update statement.
    (3) Your expected/required result after update.

  5. #5
    Join Date
    Jul 2008
    Posts
    94
    Code:
    drop table temppp
    DB20000I  The SQL command completed successfully.
    
    create table temppp(uniqueid integer,employeeid varchar(15),skillid varchar(10),sname varchar(90),prof varchar(10),acode varchar(1), adesc varchar(30),start_date date,end_date date,rind varchar(1))
    DB20000I  The SQL command completed successfully.
    
    insert into temppp values(3,10,'1','Unix','Basic','1','ABC',DATE(CURRENT DATE-1 DAY), DATE(CURRENT DATE-1 DAY),'N')
    DB20000I  The SQL command completed successfully.
    
    insert into temppp values(4,10,'1','Unix Shell','Basic','2','XYZ',CURRENT DATE, NULL,'Y')
    DB20000I  The SQL command completed successfully.
    
    insert into temppp values(5,10,'2','Pro*C','Basic','1','ABC',DATE(CURRENT DATE-1 DAY), DATE(CURRENT DATE-1 DAY),'N')
    DB20000I  The SQL command completed successfully.
    
    insert into temppp values(5,10,'2','Pro*C','Expert','2','XYZ',CURRENT DATE, NULL,'Y')
    DB20000I  The SQL command completed successfully.
    
    insert into temppp values(8,10,'5','Perl','Expert','1','XYZ',CURRENT DATE, NULL,'Y')
    DB20000I  The SQL command completed successfully.
    
    insert into temppp values(1,10,'3','Java','Basic','1','ABC',DATE(CURRENT DATE-1 DAY), DATE(CURRENT DATE-1 DAY),'N')
    DB20000I  The SQL command completed successfully.
    
    insert into temppp values(2,10,'3','Sun Java','Basic','2','XYZ',CURRENT DATE, NULL,'Y')
    DB20000I  The SQL command completed successfully.
    
    insert into temppp values(3,11,'3','Java','Basic','1','ABC',DATE(CURRENT DATE-1 DAY), DATE(CURRENT DATE-1 DAY),'N')
    DB20000I  The SQL command completed successfully.
    
    insert into temppp values(4,11,'3','Sun Java','Basic','2','XYZ',CURRENT DATE, NULL,'Y')
    DB20000I  The SQL command completed successfully.
    
    insert into temppp values(1,11,'1','Unix','Basic','1','ABC',DATE(CURRENT DATE-1 DAY), DATE(CURRENT DATE-1 DAY),'N')
    DB20000I  The SQL command completed successfully.
    
    insert into temppp values(2,11,'1','A Unix','Basic','2','XYZ',CURRENT DATE, NULL,'Y')
    DB20000I  The SQL command completed successfully.
    
    insert into temppp values(1,12,'1','Unix','Basic','1','ABC',DATE(CURRENT DATE-1 DAY), DATE(CURRENT DATE-1 DAY),'N')
    DB20000I  The SQL command completed successfully.
    
    insert into temppp values(2,12,'1','Unix','Basic','2','XYZ',CURRENT DATE, NULL,'Y')
    DB20000I  The SQL command completed successfully.
    
    Update temppp g set g.acode='6', g.adesc='M XYZ' where (g.employeeid,g.uniqueid) in (Select f.employeeid,f.uniqueid from (select e.employeeid,e.uniqueid, rank() over(partition by e.employeeid,e.skillid order by e.uniqueid) as M_change, rank() over(partition by e.employeeid,e.skillid order by e.sname,e.prof) as B_change from (select a.* from temppp a where a.end_date=DATE(CURRENT DATE-1 DAY) and a.rind='N' union all select c.* from temppp c Where c.start_date=CURRENT DATE and c.acode='2' and c.rind='Y') e ) f where f.M_change=2 and f.B_change=1 ) and g.acode='2' and g.rind='Y'
    DB20000I  The SQL command completed successfully.
    
    select * from temppp
    
    UNIQUEID    EMPLOYEEID      SKILLID    SNAME                                                                                      PROF       ACODE ADESC                          START_DATE END_DATE   RIND
    ----------- --------------- ---------- ------------------------------------------------------------------------------------------ ---------- ----- ------------------------------ ---------- ---------- ----
              3 10              1          Unix                                                                                       Basic      1     ABC                            10/26/2011 10/26/2011 N   
              4 10              1          Unix Shell                                                                                 Basic      2     XYZ                            10/27/2011 -          Y   
              5 10              2          Pro*C                                                                                      Basic      1     ABC                            10/26/2011 10/26/2011 N   
              5 10              2          Pro*C                                                                                      Expert     2     XYZ                            10/27/2011 -          Y   
              8 10              5          Perl                                                                                       Expert     1     XYZ                            10/27/2011 -          Y   
              1 10              3          Java                                                                                       Basic      1     ABC                            10/26/2011 10/26/2011 N   
              2 10              3          Sun Java                                                                                   Basic      2     XYZ                            10/27/2011 -          Y   
              3 11              3          Java                                                                                       Basic      1     ABC                            10/26/2011 10/26/2011 N   
              4 11              3          Sun Java                                                                                   Basic      2     XYZ                            10/27/2011 -          Y   
              1 11              1          Unix                                                                                       Basic      1     ABC                            10/26/2011 10/26/2011 N   
              2 11              1          A Unix                                                                                     Basic      6     M XYZ                          10/27/2011 -          Y   
              1 12              1          Unix                                                                                       Basic      1     ABC                            10/26/2011 10/26/2011 N   
              2 12              1          Unix                                                                                       Basic      6     M XYZ                          10/27/2011 -          Y   
    
      13 record(s) selected.
    
    Expected Result
    
    UNIQUEID    EMPLOYEEID      SKILLID    SNAME                                                                                      PROF       ACODE ADESC                          START_DATE END_DATE   RIND
    ----------- --------------- ---------- ------------------------------------------------------------------------------------------ ---------- ----- ------------------------------ ---------- ---------- ----
              3 10              1          Unix                                                                                       Basic      1     ABC                            10/26/2011 10/26/2011 N   
              4 10              1          Unix Shell                                                                                 Basic      2     XYZ                            10/27/2011 -          Y   
              5 10              2          Pro*C                                                                                      Basic      1     ABC                            10/26/2011 10/26/2011 N   
              5 10              2          Pro*C                                                                                      Expert     2     XYZ                            10/27/2011 -          Y   
              8 10              5          Perl                                                                                       Expert     1     XYZ                            10/27/2011 -          Y   
              1 10              3          Java                                                                                       Basic      1     ABC                            10/26/2011 10/26/2011 N   
              2 10              3          Sun Java                                                                                   Basic      2     XYZ                            10/27/2011 -          Y   
              3 11              3          Java                                                                                       Basic      1     ABC                            10/26/2011 10/26/2011 N   
              4 11              3          Sun Java                                                                                   Basic      2     XYZ                            10/27/2011 -          Y   
              1 11              1          Unix                                                                                       Basic      1     ABC                            10/26/2011 10/26/2011 N   
              2 11              1          A Unix                                                                                     Basic      2     XYZ                          10/27/2011 -          Y   
              1 12              1          Unix                                                                                       Basic      1     ABC                            10/26/2011 10/26/2011 N   
              2 12              1          Unix                                                                                       Basic      6     M XYZ                          10/27/2011 -          Y

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    First of all, those in your update statement may be not necessary.
    a) UNION ALL
    b) nested-subquery: "e"
    c) qualifiers: "a", "c", "e", "f", and "g"
    d) DATE function: ... where a.end_date=DATE(CURRENT DATE-1 DAY) and ...

    Code:
    /* Remove UNION ALL, nested-subquery "e" and qualifiers("a", "c", "e", "f", and "g"). */
    Update temppp g
       set acode = '6'
         , adesc = 'M XYZ'
     where (employeeid , uniqueid)
           in (Select employeeid , uniqueid
                from  (select employeeid , uniqueid
                            , rank() over(partition by employeeid , skillid
                                                   order by uniqueid
                                         ) as M_change
                            , rank() over(partition by employeeid , skillid
                                              order by sname , prof
                                         ) as B_change
                        from  temppp
                        where end_date   = CURRENT DATE - 1 DAY
                          and rind       = 'N'
                          OR  start_date = CURRENT DATE
                          and rind       = 'Y'
                          and acode      = '2'
                      ) f
                where M_change = 2
                  and B_change = 1
              )
       and acode = '2'
       and rind  = 'Y'
    ;
    The reason of being updated extra row must be that B_change is reverse order to M_change in the group,
    see last 6 rows with M_change and B_change.
    Code:
    UNIQUEID    EMPLOYEEID SKILLID    SNAME      PROF       ACODE ADESC START_DATE END_DATE   RIND  M_change B_change
    ----------- ---------- ---------- ---------- ---------- ----- ------ ---------- ---------- ---- -------- --------
              3 11         3          Java       Basic      1     ABC    10/26/2011 10/26/2011 N    1        1   
              4 11         3          Sun Java   Basic      2     XYZ    10/27/2011 -          Y    2        2
    
              1 11         1          Unix       Basic      1     ABC    10/26/2011 10/26/2011 N    1        2       <--- B_change is reverse order to M_change
              2 11         1          A Unix     Basic      6     M XYZ  10/27/2011 -          Y    2        1       <--- B_change is reverse order to M_change
    
              1 12         1          Unix       Basic      1     ABC    10/26/2011 10/26/2011 N    1        1
              2 12         1          Unix       Basic      6     M XYZ  10/27/2011 -          Y    2        1
    A way to correct this issue may be to apply MAX olap specifications to M_change and B_change, like...

    Example 1:
    Code:
    Update temppp g
       set acode = '6'
         , adesc = 'M XYZ'
     where (employeeid , uniqueid)
           in (Select employeeid , uniqueid
                from  (select employeeid , uniqueid
                            , MAX( rank() over(partition by employeeid , skillid
                                                   order by uniqueid
                                              )
                                 ) OVER(partition by employeeid , skillid
                                       ) as M_change
                            , MAX( rank() over(partition by employeeid , skillid
                                                   order by sname , prof
                                              )
                                 ) OVER(partition by employeeid , skillid
                                       ) as B_change
                        from  temppp
                        where end_date   = CURRENT DATE - 1 DAY
                          and rind       = 'N'
                          OR  start_date = CURRENT DATE
                          and rind       = 'Y'
                          and acode      = '2'
                      ) f
                where M_change = 2
                  and B_change = 1
              )
       and acode = '2'
       and rind  = 'Y'
    ;

    Another example.

    Example 2:
    Code:
    Update temppp g
       set acode = '6'
         , adesc = 'M XYZ'
     where (employeeid , skillid)
           in (Select employeeid , skillid
                from  temppp
                where end_date   = CURRENT DATE - 1 DAY
                  and rind       = 'N'
                  OR  start_date = CURRENT DATE
                  and rind       = 'Y'
                  and acode      = '2'
                GROUP BY
                      employeeid , skillid
                HAVING
                      MAX(uniqueid) > MIN(uniqueid)
                  AND MAX(sname)    = MIN(sname)
              )
       and acode = '2'
       and rind  = 'Y'
    ;
    Last edited by tonkuma; 10-28-11 at 08:43. Reason: Number on sample codes. Add d) DATE function to not necessary items.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    This looks well on your supplied data.

    Example 3:
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM temppp;
    ------------------------------------------------------------------------------
    
    UNIQUEID    EMPLOYEEID      SKILLID    SNAME                                                                                      PROF       ACODE ADESC                          START_DATE END_DATE   RIND
    ----------- --------------- ---------- ------------------------------------------------------------------------------------------ ---------- ----- ------------------------------ ---------- ---------- ----
              3 10              1          Unix                                                                                       Basic      1     ABC                            2011-10-27 2011-10-27 N   
              4 10              1          Unix Shell                                                                                 Basic      2     XYZ                            2011-10-28 -          Y   
              5 10              2          Pro*C                                                                                      Basic      1     ABC                            2011-10-27 2011-10-27 N   
              5 10              2          Pro*C                                                                                      Expert     2     XYZ                            2011-10-28 -          Y   
              8 10              5          Perl                                                                                       Expert     1     XYZ                            2011-10-28 -          Y   
              1 10              3          Java                                                                                       Basic      1     ABC                            2011-10-27 2011-10-27 N   
              2 10              3          Sun Java                                                                                   Basic      2     XYZ                            2011-10-28 -          Y   
              3 11              3          Java                                                                                       Basic      1     ABC                            2011-10-27 2011-10-27 N   
              4 11              3          Sun Java                                                                                   Basic      2     XYZ                            2011-10-28 -          Y   
              1 11              1          Unix                                                                                       Basic      1     ABC                            2011-10-27 2011-10-27 N   
              2 11              1          A Unix                                                                                     Basic      2     XYZ                            2011-10-28 -          Y   
              1 12              1          Unix                                                                                       Basic      1     ABC                            2011-10-27 2011-10-27 N   
              2 12              1          Unix                                                                                       Basic      2     XYZ                            2011-10-28 -          Y   
    
      13 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    Update temppp t/*arget*/
       set acode = '6'
         , adesc = 'M XYZ'
     where rind       = 'Y'
       and acode      = '2'
       AND start_date = CURRENT DATE
       AND EXISTS
           (Select 0
             from  temppp e/*xists*/
             where e.end_date   = CURRENT DATE - 1 DAY
               and e.rind       = 'N'
               AND e.employeeid = t.employeeid
               AND e.skillid    = t.skillid
               AND e.uniqueid  <> t.uniqueid
               AND e.sname      = t.sname
           )
    ;
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM temppp;
    ------------------------------------------------------------------------------
    
    UNIQUEID    EMPLOYEEID      SKILLID    SNAME                                                                                      PROF       ACODE ADESC                          START_DATE END_DATE   RIND
    ----------- --------------- ---------- ------------------------------------------------------------------------------------------ ---------- ----- ------------------------------ ---------- ---------- ----
              3 10              1          Unix                                                                                       Basic      1     ABC                            2011-10-27 2011-10-27 N   
              4 10              1          Unix Shell                                                                                 Basic      2     XYZ                            2011-10-28 -          Y   
              5 10              2          Pro*C                                                                                      Basic      1     ABC                            2011-10-27 2011-10-27 N   
              5 10              2          Pro*C                                                                                      Expert     2     XYZ                            2011-10-28 -          Y   
              8 10              5          Perl                                                                                       Expert     1     XYZ                            2011-10-28 -          Y   
              1 10              3          Java                                                                                       Basic      1     ABC                            2011-10-27 2011-10-27 N   
              2 10              3          Sun Java                                                                                   Basic      2     XYZ                            2011-10-28 -          Y   
              3 11              3          Java                                                                                       Basic      1     ABC                            2011-10-27 2011-10-27 N   
              4 11              3          Sun Java                                                                                   Basic      2     XYZ                            2011-10-28 -          Y   
              1 11              1          Unix                                                                                       Basic      1     ABC                            2011-10-27 2011-10-27 N   
              2 11              1          A Unix                                                                                     Basic      2     XYZ                            2011-10-28 -          Y   
              1 12              1          Unix                                                                                       Basic      1     ABC                            2011-10-27 2011-10-27 N   
              2 12              1          Unix                                                                                       Basic      6     M XYZ                          2011-10-28 -          Y   
    
      13 record(s) selected.
    Last edited by tonkuma; 10-28-11 at 13:15. Reason: Add comment for correlation names in Update statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •