0

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)
  • If it's running, then what's the question? If it's not, then what's the error? – mustaccio Jan 24 at 17:18
  • It was running before, sorry. It's not ever since I added the second 'WHEN MATCHED' and now it says "Token INSERT was not valid. Valid tokens: SIGNAL" – Whisou138 Jan 24 at 17:21
  • @mustaccio so it seems like the biggest issue is the additional 'WHEN MATCHED' – Whisou138 Jan 24 at 17:46
2

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 ...
  • I think that makes sense, so does that satisfy all 3 of my conditions above basically? So if it finds a match then it will only be a non-expired record – Whisou138 Jan 24 at 18:09
  • It's not exactly clear what your plan is for p.expire_date = current_date, but otherwise I think it should do what you want. – mustaccio Jan 24 at 18:11
  • Thanks. It's basically just keeping track of products to customer and setting a date when we should expect more purchases. So they will have records for the same exact product but if it's within the expiration window then I just want to update but if it's expired OR doesn't exist at all, then inserting – Whisou138 Jan 24 at 18:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.