Can I use a Recursive call to mark a previous row

Howardw · #1 (**permalink**) 02-25-10, 08:45

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

ARWinner · #2 (**permalink**) 02-25-10, 09:49

I do not understand what you want to do. Please explain it in more detail. Some examples might be helpful.

Andy

dav1mo · #3 (**permalink**) 02-25-10, 10:09

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

tonkuma · #4 (**permalink**) 02-25-10, 10:20

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)

tonkuma · #5 (**permalink**) 02-25-10, 10:55

Quote:

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'
.....

tonkuma · #6 (**permalink**) 02-25-10, 11:07

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'
.....

Internet Services

Web Development

Digital Marketing

Consumer Tech