CODEWITHSUNDEEP
sqldb2
Nohsib
Nohsib

Reputation: 3712

How to have AND & OR condition in SQL Merge statement for DB2

I have a merge statement which works when I did not have to consider null values :

This works :

MERGE INTO LTABLE L
USING (SELECT 1392 UCL_USER_ID,11 REGISTER_ID ,5 REGION_ID  FROM DUAL ) B
    ON (    L.UCL_USER_ID = B.UCL_USER_ID
            AND L.REGISTER_ID = B.REGISTER_ID
            AND (L.REGION_ID = B.REGION_ID)
        )
WHEN NOT MATCHED
    THEN
        INSERT (
            L.LTABLE_ID
            ,L.UCL_USER_ID
            ,L.REGISTER_ID
            ,L.REGION_ID
            )
        VALUES (
            SEQ_LTABLE_ID.NEXTVAL
            ,1392
            ,11
            ,5);

When I have to consider null values for REGION_ID the below works :

MERGE INTO LTABLE L
USING (SELECT 1392 UCL_USER_ID,11 REGISTER_ID ,NULL REGION_ID   FROM DUAL ) B
    ON (    L.UCL_USER_ID = B.UCL_USER_ID
            AND L.REGISTER_ID = B.REGISTER_ID
            AND (L.REGION_ID IS NULL AND B.REGION_ID IS NULL)
        )
WHEN NOT MATCHED
    THEN
        INSERT (
            L.LTABLE_ID
            ,L.UCL_USER_ID
            ,L.REGISTER_ID
            ,L.REGION_ID
            )
        VALUES (
            SEQ_LTABLE_ID.NEXTVAL
            ,1392
            ,11
            ,NULL);

Question is how can I combine these two conditions when it can be null or some numeric value. I tried the below but sql developer gives the error that query is not right.

AND ((L.REGION_ID = B.REGION_ID) OR (L.REGION_ID IS NULL AND B.REGION_ID IS NULL))

ERROR :

SQL Error: DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610, SQLERRMC=FCS already resolved to different type, DRIVER=4.17.29

Upvotes: 1

Views: 1535

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

This is just a guess, but perhaps the problem is the default type for NULL. Perhaps a cast will fix the problem:

MERGE INTO LTABLE L
USING (SELECT 1392 UCL_USER_ID, 11 as REGISTER_ID,
              CAST(NULL as VARCHAR(255)) as REGION_ID
       FROM DUAL ) B
    ON (    L.UCL_USER_ID = B.UCL_USER_ID
            AND L.REGISTER_ID = B.REGISTER_ID
            AND (L.REGION_ID IS NULL AND B.REGION_ID IS NULL)
        )

Or whatever the appropriate type is for REGION_ID.

Upvotes: 1

Related Questions