Sample Header Ad - 728x90

Using a DB2 merge with conditionals for matches?

0 votes
1 answer
414 views
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)
Asked by Whisou138 (103 rep)
Jan 24, 2019, 05:13 PM
Last activity: Jan 24, 2019, 06:07 PM