Any way we can capture number of rows that was updated in update statement results log?

[jay_q]

Hello

I have a inner loop which has a series Update statements which I need to loop it until update statement results from step N-1 is equal to one from Step N. Then I have to go back to outer loop.

the inner loop syntax would be:

Inner loop:

     %do J = 1 %to 200;

          a series update statements...

                    %if (update statement results from step J) <>(update statement results from step J-1) %then %goto leave_inner;

                    /* (in other words, if 1 row was updated in WORK.DATA from Step J-1 but  0 row was updated in WORK.DATA from StepJ, then we use %goto to leave the inner loop)*/

                      %end;

      %leave_inner: /* leave inner loop to outer loop*/

if there is a way to capture just the number of rows that was updated in update statement results log and then put it into a macro, that would solve my problem as well.

Thanks in advance for any ideas.

Lei

[PGStats · Accepted solution]

Assuming that "update statements" refer to SQL UPDATE statements, you could get the number of records updated (not necessarily changed) after each query from the SQLOBS automatic macro variable.

PG

[jay_q]

tested and works! Thank you. PGStars.