DB2 – Using Merge with Conditionals for Matches

db2iseriesmergePHP

Within a PHP script I'm running a working merge but I need to add date conditionals to it, and my updates cause it to no longer work.

The logic I'm looking for is this:

  1. If there's a match AND the expire_date is before today, do an insert
  2. If there's a match AND the expire_date is after today, do an update
  3. If no match, just insert

The matching has been working but I basically want to disregard matches that are expired. What am I doing wrong here?

This is running in db2 for iseries

                    MERGE INTO products AS P
                    USING ( VALUES (
                        CAST(:CUSTOMER AS INT),
                        CAST(:SLSCODE AS INT),
                        CAST(:DTL1 AS INT),
                        CAST(:DTL2 AS INT),
                        CAST(:DTL3 AS INT),
                        CAST(:COUNT AS INT),
                        CAST(:LAST_DATE AS DATE),
                        CAST(:FLAG AS SMALLINT),
                        CAST(:ORDER AS INT),
                        CAST(:PIECES AS VARCHAR(45))
                        )
                    )
                AS S(CUSTOMER,REGION,DTL1,DTL2,DTL3,COUNT,LAST_DATE,FLAG,ORDER,PIECES)
                ON s.CUSTOMER = p.CUSTOMER and s.REGION = p.REGION and s.DTL1 = p.DTL1 and s.DTL2 = p.DTL2 and s.DTL3 = p.DTL3 and s.COUNT = p.COUNT and s.LAST_DATE = p.LAST_DATE and s.FLAG = p.FLAG and s.ORDER = p.ORDER AND s.PIECES = p.PIECES


                WHEN MATCHED AND p.expire_date > current_date
                    THEN UPDATE SET  last_date = s.last_date, order = s.order, pieces = s.pieces, expire_date = s.expire_date

                WHEN MATCHED AND p.expire_date < current_date
                    THEN INSERT VALUES (s.customer,s.region, s.dtl1, s.dtl2, s.dtl3, s.count, s.last_date, s.flag, s.order, s.pieces, s.expire_date)

                WHEN NOT MATCHED
                    THEN INSERT VALUES (s.customer,s.region, s.dtl1, s.dtl2, s.dtl3, s.count, s.last_date, s.flag, s.order, s.pieces, s.expire_date)

Best Answer

The manual says WHEN MATCHED does not allow INSERT. However, you should be able to use the expire_date condition in the USING clause:

MERGE INTO products AS P
USING ( VALUES (
...
    )
)
AS S(CUSTOMER,REGION,DTL1,DTL2,DTL3,COUNT,LAST_DATE,FLAG,ORDER,PIECES)
ON s.CUSTOMER = p.CUSTOMER 
and s.REGION = p.REGION 
and ...
and p.expire_date > current_date
WHEN MATCHED 
THEN UPDATE ...
WHEN NOT MATCHED
THEN INSERT ...
Related Question