Re: MERGE w/SIGNAL how to get rows in error?

Say you're using an SQL stored procedure with a MERGE statement that uses
`signal-statement` as shown in the example in the SQL Reference

merge into archive ar using (
select activity
,description
,date
,last_modified
from activities_groupa
) ac on (ar.activity = ac.activity)
and ar.group = 'A'
when matched and ac.date is null then signal sqlstate '70001'
set message_text = 'Activity cannot be modified. Reason: date is not
known'
when matched and ac.date < current date then delete
when matched and as.last_modified < ac.last_modified then update set (
description
,date
,last_modified
) = (ac.description, ac.date, default)
when not matched and ac.date is null then signal sqlstate '70002'
set message_text = 'Activity cannot be inserted. Reason: date is not
known'
when not matched and ac.date >= current date then insert (
group
,activity
,description
,date
) values (
'A'
,ac.activity
,ac.description
,ac.date
)
else ignore


The manual also says:

GET DIAGNOSTICS considerations: If a MERGE statement completes with one
or more errors, the GET DIAGNOSTICS statement can be used after the MERGE
statement to check which input row(s) failed. The GET DIAGNOSTICS
statement-information-item, NUMBER, indicates the number of conditions
(errors
of warnings) detected by execution of the MERGE statement. For each
condition,
the GET DIAGNOSTICS condition-information-item, DB2_ROW_NUMBER, indicates
the
input source row that caused an error.


Question, how do we get back the rows in error?

I was thinking
GET DIAGNOSTICS nbrRows = NUMBER;
for x= 1 to nbrRows
GET DIAGNOSTICS CONDITION X
rowNbr = DB2_ROW_NUMBER
, msg = MESSAGE_TEXT;
end for;

But SQL's FOR doesn't work like that. Looking at alternatives such as
LOOP, I
see:

Considerations for the diagnostics area: At the beginning of the first
iteration of the LOOP statement, and with every subsequent iteration, the
diagnostics area is cleared.


Clear the diagnostic area I'm trying to read doesn't seem like a good idea.

What am I missing? Is a GOTO loop the only answer?

Thanks!
Charles