SQL Error Handling

[Viking]

Hi,

I have an SQL insert statement that runs fine and the desired records are inserted. But sometimes, maybe one of the records it's trying to insert already exists and therefore produces a duplicate record error. I would like it to handle this error so that the statement doesn't fail and at least the rest of the records are inserted, just skipping the duplicate. How can I monitor for sqlstt = '23505' as part of my statement so that it inserts all the records it can and just skips those that are duplicates?

For example, how would I integrate that error handling into this simple example statement:

Insert into MYFILE(KEY1, FIELD2, MYTS1, MYTS2)
select ACCT, STRNO, '0001-01-01', '0001-01-01'
from MYFROMFILE
where STS = 'A'
and SYR = 22 and SMO >= 4 and SDT >= 1

I will run this kind of statement interactively sometimes but will also be putting it into an SQLRPGLE program with variables for the criteria, such as the year, month, and day.

TIA!

[Reboot1970]

Hi,

Maybe use MERGE statement.

[Vectorspace]

MERGE lets you insert from a join, so you can only insert records that dont exist in the destination table

https://www.itjungle.com/2010/09/22/fhg092210-story02/

[Reboot1970]

MERGE lets you insert from a join, so you can only insert records that dont exist in the destination table
https://www.itjungle.com/2010/09/22/fhg092210-story02/

You can insert, update and delete.

[Viking]

So, using the MERGE statement, are you suggesting something like this?


MERGE INTO MYLIB.MYFILE AS MYFILE_TARGET USING MYLIB.MYFROMFILE AS MYFROMFILE_SOURCE
ON (MYFILE_TARGET.KEY1 = MYFROMFILE_SOURCE.ACCT)
WHEN NOT MATCHED THEN
INSERT (KEY1, FIELD2, MYTS1, MYTS2) VALUES(ACCT, STRNO, '0001-01-01', '0001-01-01')
ELSE IGNORE

[Vectorspace]

Yes, something like that

[Reboot1970]

You, can also update or delete when matched.

[Viking]

Doing it using the MERGE method shown in my example above works great. Thanks for the guidance!

[Viking]

Vectorspace , I tried using a CTE as temp table F and then using F in my MERGE statement above as the "from" (source) file but that didn't work. I thought I would try that in order to limit up front the number of records it would need to join but it didn't seem to like a CTE used in the MERGE.

[B.Hauser]

Common Table Expressions are part of an SELECT statement ... and nothing else

[Brian Rusch]

You can't use a CTE in a MERGE, but you can use inline SQL:

MERGE INTO MYLIB.MYFILE AS MYFILE_TARGET USING (Select * from MYLIB.MYFROMFILE where field = 'value') AS MYFROMFILE_SOURCE
ON (MYFILE_TARGET.KEY1 = MYFROMFILE_SOURCE.ACCT)
WHEN NOT MATCHED THEN
INSERT (KEY1, FIELD2, MYTS1, MYTS2) VALUES(ACCT, STRNO, '0001-01-01', '0001-01-01')
ELSE IGNORE

[Viking]

Brian Rusch , that's a good idea - thanks!