Oppolzer - Informatik / Blog

I guess that you copied the SQLCODE into the variable L_SQLCODE before entering
the IF statement where you do the GET DIAGNOSTICS on the ROW COUNT.

But the IF statement will not be entered, because the SQLCODE after the DELETE
is 0, because rows to DELETE have been found, so maybe this is the reason that
your variable L_COUNT stays at the value zero.

Another issue: if you do mass DELETEs without a WHERE condition on Segmented
Tablespaces, DELETE will not be able to count the number of rows that have been
deleted, and the value in SQLERRD(3) will be -1. Don't know about the returned
value from GET DIAGNOSTICS / ROW COUNT.

Kind regards

Bernd




Am 21.10.2012 11:52, schrieb P.V.:
>
> M.,
>
> There must be some details that you don't show us, so it's difficult
> to spot the problem.
>
> E.g., the program should be consulting SQLCODE, not L_SQLCODE. And it
> should have at least "LANGUAGE SQL". And there is no P1 to leave.
>
> Could you try to get this simplified version of your procedure
> compile, i.e. after changing "L_SQLCODE" to "SQLCODE" and adding
> "LANGUAGE SQL" ? And verifying whether now PO_COUNT was 2?
>
> And then once again, now removing "ROLLBACK" ?
>
> -- P.
>
>
> In Reply to M.N.:
>
> CREATE PROCEDURE RECONCILE.BURFSH1(
> OUT PO_ERROR INT ,
> IN PI_WORKING_DATE CHAR(4),
> IN PI_WORKING_YEAR CHAR(4),
> IN PI_RECONCILIATION_FILE_ID DECIMAL(19,0),
> OUT PO_COUNT DECIMAL(31,0)
> )
>
> DECLARE SP_TYPE INT DEFAULT 0 ;
> DECLARE PO_WDID DECIMAL(19,0) DEFAULT 0 ;
> DECLARE L_COUNT DECIMAL(31,0) DEFAULT 0 ;
>
> DELETE
> FROM RECONCILE.RECONCILIATION_NONUS NO
> WHERE NO.RECONCILIATION_FILE_ID =PI_RECONCILIATION_FILE_ID;
>
> -----------------------------
>
> *IF (L_SQLCODE <>0 ) THEN
> SET PO_ERROR= L_SQLCODE ; **
> GET DIAGNOSTICS L_COUNT = ROW_COUNT; **
> SET PO_COUNT= L_COUNT;
> ROLLBACK;
> LEAVE P1;
> END IF ;
> *
>
> *that's source *
>
> *deleted statment delete 2 rows from table but row_count returned
> '0' *
>
> *i can't underestan why it returns '0'????????
> *
>
>
> -----End Original Message-----