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

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Nobody's played with this?

Or did I just post the question too close to a long weekend for most :)

On Fri, Sep 3, 2021 at 2:32 PM Charles Wilt <charles.wilt@xxxxxxxxx> wrote:

> 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