Merge query in H2 database giving "Unknown data type:" error

Question

when merge native query is used using JPA, it is resulting into following error:

{"ts":"2022-05-05T17:03:12.124+0000","level":"ERROR","message":"SQL Error: 50004, SQLState: HY004 Unknown data type: "SYS_USER_ID"; SQL statement:\nMERGE INTO USER TARGET USING (SELECT ? AS USER_ID, ? CLNT_ID, ? WPI, ? U_TYPE_CD, ? ENBL_SW, ? TRD FROM DUAL ) SOURCE ON(TARGET.TRD = SOURCE.TRD AND TARGET.CLNT_ID = SOURCE.CLNT_ID AND TARGET.USER_ID = SOURCE.USER_ID ) WHEN MATCHED THEN UPDATE SET TARGET.WPI = SOURCE.WPI, TARGET.U_TYPE_CD = SOURCE.U_TYPE_CD, TARGET.ENBL_SW = SOURCE.ENBL_SW WHEN NOT MATCHED THEN INSERT (USER_ID, USER_ID , CLNT_ID, WPI, U_TYPE_CD, ENBL_SW, TRD) VALUES (11001 , ? , ?, ?, ?, ?, ?)

Above query is working very well with Oracle db - not working in H2 database.

Answer 1

The sql in your call stack shows:

MERGE INTO USER 
    TARGET 
    USING (SELECT ? AS USER_ID, ? CLNT_ID, ? WPI, ? U_TYPE_CD, ? ENBL_SW, ? TRD FROM DUAL ) 
    SOURCE 
    ON(TARGET.TRD = SOURCE.TRD AND TARGET.CLNT_ID = SOURCE.CLNT_ID AND TARGET.USER_ID = SOURCE.USER_ID ) 
    WHEN MATCHED THEN UPDATE SET TARGET.WPI = SOURCE.WPI, TARGET.U_TYPE_CD = SOURCE.U_TYPE_CD, TARGET.ENBL_SW = SOURCE.ENBL_SW 
    WHEN NOT MATCHED THEN INSERT (USER_ID, USER_ID , CLNT_ID, WPI, U_TYPE_CD, ENBL_SW, TRD) 
    VALUES (11001 , ? , ?, ?, ?, ?, ?)

You need check the above SQL executing result in your H2 database. The better is that you can provide the whole reproducing code line for a check. Different DBMS can have different result. Not all the same.