CODEWITHSUNDEEP
sqlmergedb2
Geoff_S
Geoff_S

Reputation: 5107

Merge in db2 error for invalid attributes

I'm using a newly created merge statement to perform UPSERTS in db2.

I think the structure is correct but the problem is I get the error

sqlstate[42000] syntax or access violation: Attributes not valid

I'm not sure exactly where the issue is because it's not very explicit but it seems like maybe it's coming from one of my values?

the merge:

MERGE INTO STATUS as S
    USING (VALUES(
                CAST(:ORDER as INT),
                CAST(:STATUS as VARCHAR),
                CAST(:IS_ACTIVE as DECIMAL(1,0)),
                CAST(:DATE_UPDATED as DATE)
                )
            )
    AS O(order, status, is_active, date_updated)    
    ON o.order = S.order_id 
WHEN MATCHED THEN UPDATE SET order_status = status, is_active = is_active, date_updated = date_updated
WHEN NOT MATCHED THEN INSERT VALUES(order, status, is_active, date_updated)

Upvotes: 1

Views: 507

Answers (1)

Paul Vernon
Paul Vernon

Reputation: 3901

The DB2 for iSeries manual https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzmerge.htm has the following example.

MERGE INTO employee AS t
   USING (VALUES(CAST(? AS CHAR(6)), CAST(? AS VARCHAR(12)),
                      CAST(? AS CHAR(1)), CAST(? AS VARCHAR(15)),
                      CAST(? AS SMALLINT), CAST(? AS INTEGER)))
          s (empno, firstnme, midinit, lastname, edlevel, salary)
   ON t.empno = s.empno
   WHEN MATCHED THEN
     UPDATE SET salary = s.salary
   WHEN NOT MATCHED THEN 
     INSERT (empno, firstnme, midinit, lastname, edlevel, salary)
            VALUES (s.empno, s.firstnme, s.midinit, s.lastname, s.edlevel,
                    s.salary)

Maybe you need to qualify the attribute names in the SET and the final VALUES statement.

E.g. saying is_active = is_active rather than is_active = O.is_active is probably confusing for DB2

Upvotes: 1

Related Questions