Results 1 to 6 of 6
  1. #1
    Join Date
    Nov 2009
    Posts
    20

    Unanswered: Can I use a Recursive call to mark a previous row

    DB2 9.5 LUW Windows server 2003
    I have a table by dated event and I am trying to identify changes of events and flag them - this is currently done using a program
    Table
    Date start date end Loc Bin Qty status
    01/02/2004 01/05/2004 X1 X2 20 C
    02/05/2004 01/07/2004 X1 X2 30 N
    02/07/2004 15/10/2004 X2 X2 30 C
    16/10/2004 21/02/2005 X2 X3 10 CL
    22/02/2005 15/09/2006 X2 X3 40 N

    I need to identify when a row changes Loc or Bin and flag it and also the mark the last row that changed Loc or Bin with different code

    Can this be done with a comlex query or do i need to use cursors and a program

    If i can do it with a query a simple example would help

    Thanks

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    I do not understand what you want to do. Please explain it in more detail. Some examples might be helpful.

    Andy

  3. #3
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    sounds like you want a trigger on the table. within the trigger you could flag the row in the manner you want and issue an update to the prior row.

    Dave

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If Loc and Bin were changed to greater value than before, this would be an answer.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    dated_event(date_start , date_end , Loc , Bin , Qty /*, status*/ ) AS (
    SELECT DATE(date_start) , DATE(date_end)
         , Loc , Bin , Qty
      FROM(VALUES
              ('01.02.2004' , '01.05.2004' , 'X1' , 'X2' , 20 /*, 'C'*/ )
            , ('02.05.2004' , '01.07.2004' , 'X1' , 'X2' , 30 /*, 'N'*/ )
            , ('02.07.2004' , '15.10.2004' , 'X2' , 'X2' , 30 /*, 'C'*/ )
            , ('16.10.2004' , '21.02.2005' , 'X2' , 'X3' , 10 /*, 'CL'*/ )
            , ('22.02.2005' , '15.09.2006' , 'X2' , 'X3' , 40 /*, 'N'*/ )
          ) s(date_start , date_end , Loc , Bin , Qty /*, status*/ )
    )
    SELECT de.*
         , CASE
           WHEN loc = LAG(loc, 1) OVER(ORDER BY date_start)
            AND bin = LAG(bin, 1) OVER(ORDER BY date_start)
           THEN 'N'
           ELSE 'C'
             || CASE
                WHEN RANK() OVER(ORDER BY loc || bin DESC) = 1
                THEN 'L'
                ELSE ''
                END
           END  AS status
      FROM dated_event de
     ORDER BY
           date_start
    ;
    ------------------------------------------------------------------------------
    
    DATE_START DATE_END   LOC BIN QTY         STATUS
    ---------- ---------- --- --- ----------- ------
    2004-02-01 2004-05-01 X1  X2           20 C     
    2004-05-02 2004-07-01 X1  X2           30 N     
    2004-07-02 2004-10-15 X2  X2           30 C     
    2004-10-16 2005-02-21 X2  X3           10 CL    
    2005-02-22 2006-09-15 X2  X3           40 N     
    
      5 record(s) selected.
    You can use MAX() OVER() specification instead of LAG function on DB2 for LUW 9.1 or older, like this.
    Code:
           WHEN loc = MAX(loc) OVER(ORDER BY date_start
                                     ROWS BETWEEN 1 PRECEDING
                                              AND 1 PRECEDING)
            AND bin = MAX(bin) OVER(ORDER BY date_start
                                     ROWS BETWEEN 1 PRECEDING
                                              AND 1 PRECEDING)
    Last edited by tonkuma; 02-25-10 at 11:38. Reason: Replace LAG and MAX() OVER(). Change data type of date_start and date_end to DATE from VARCHAR.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If Loc and Bin were changed to greater value than before, this would be an answer.
    Without this assumption, following example would be an answer.
    Code:
    SELECT de.*
         , CASE
           WHEN loc = LAG(loc, 1) OVER(ORDER BY date_start)
            AND bin = LAG(bin, 1) OVER(ORDER BY date_start)
           THEN 'N'
           ELSE 'C'
             || CASE
                WHEN loc || bin
                     = LAST_VALUE(loc || bin)
                         OVER(ORDER BY date_start
                              RANGE BETWEEN CURRENT ROW
                                        AND UNBOUNDED FOLLOWING)
                THEN 'L'
                ELSE ''
                END
           END  AS status
      FROM dated_event de
     ORDER BY
           date_start
    ;
    ------------------------------------------------------------------------------
    
    DATE_START DATE_END   LOC BIN QTY         STATUS
    ---------- ---------- --- --- ----------- ------
    2004-02-01 2004-05-01 X1  X2           20 C     
    2004-05-02 2004-07-01 X1  X2           30 N     
    2004-07-02 2004-10-15 X2  X2           30 C     
    2004-10-16 2005-02-21 X2  X3           10 CL    
    2005-02-22 2006-09-15 X2  X3           40 N     
    
      5 record(s) selected.
    or use the following expression to flag 'N'.
    Code:
    .....
           WHEN loc || bin
                = LAG(loc || bin , 1) OVER(ORDER BY date_start)
           THEN 'N'
    .....
    Last edited by tonkuma; 02-25-10 at 12:54. Reason: Add an alternative expression to flag 'N'.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If your DB2 doesn't support any of LAG or LAST_VALUE function, please try following example.

    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    dated_event(date_start , date_end , Loc , Bin , Qty /*, status*/ ) AS (
    SELECT DATE(date_start) , DATE(date_end)
         , Loc , Bin , Qty
      FROM(VALUES
              ('01.02.2004' , '01.05.2004' , 'X1' , 'X2' , 20 /*, 'C'*/ )
            , ('02.05.2004' , '01.07.2004' , 'X1' , 'X2' , 30 /*, 'N'*/ )
            , ('02.07.2004' , '15.10.2004' , 'X2' , 'X2' , 30 /*, 'C'*/ )
            , ('16.10.2004' , '21.02.2005' , 'X2' , 'X3' , 10 /*, 'CL'*/ )
            , ('22.02.2005' , '15.09.2006' , 'X2' , 'X3' , 40 /*, 'N'*/ )
          ) s(date_start , date_end , Loc , Bin , Qty /*, status*/ )
    )
    SELECT de.*
         , CASE
           WHEN loc = MAX(loc) OVER(ORDER BY date_start
                                     ROWS BETWEEN 1 PRECEDING
                                              AND 1 PRECEDING)
            AND bin = MAX(bin) OVER(ORDER BY date_start
                                     ROWS BETWEEN 1 PRECEDING
                                              AND 1 PRECEDING)
           THEN 'N'
           ELSE 'C'
             || CASE
                WHEN MAX(loc || bin)
                       OVER(ORDER BY date_start
                            RANGE BETWEEN CURRENT ROW
                                      AND UNBOUNDED FOLLOWING)
                   = MIN(loc || bin)
                       OVER(ORDER BY date_start
                            RANGE BETWEEN CURRENT ROW
                                      AND UNBOUNDED FOLLOWING)
                THEN 'L'
                ELSE ''
                END
           END  AS status
      FROM dated_event de
     ORDER BY
           date_start
    ;
    ------------------------------------------------------------------------------
    
    DATE_START DATE_END   LOC BIN QTY         STATUS
    ---------- ---------- --- --- ----------- ------
    2004-02-01 2004-05-01 X1  X2           20 C     
    2004-05-02 2004-07-01 X1  X2           30 N     
    2004-07-02 2004-10-15 X2  X2           30 C     
    2004-10-16 2005-02-21 X2  X3           10 CL    
    2005-02-22 2006-09-15 X2  X3           40 N     
    
      5 record(s) selected.
    or use the following code to flag 'N'.
    Code:
    .....
         , CASE
           WHEN loc || bin
                = MAX(loc || bin)
                     OVER(ORDER BY date_start
                          ROWS BETWEEN 1 PRECEDING
                                   AND 1 PRECEDING)
           THEN 'N'
    .....

Posting Permissions

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