Hello 'stwizard',

Thursday, May 12, 2016, 12:46:25 AM, you wrote:

> Here is my simple Stored Procedure. It simply looks for any
> address in the ADDRESS table that starts with ‘0 ‘ as in “0 SE
> ADAMS ST” and COUNT(*) how many time it might have been used in
> PER_ADDRESS and if COUNT() > 0 delete the links from the PER_ADDRESS table.

>

> The next line verifies that there is no remaining links in the
> PER_ADDRESS table and then deletes the record from the ADDRESS table.

>

> My problem is that even though ADDR_ID 347006 does not exist in the
> PER_ADDRESS table, the “IF (NOT(EXISTS(SELECT 1..” line thinks there
> is and skips the deletion of the record f orm the ADDRESS table.


> What might I be doing wrong?

First of all, this is an executable SP, not intended to return a
result set, so get rid of the RETURNS parameters and declare variables
instead. While executable SPs *can* return a single-row result set,
with your for-loop, the only result you would get back would be the
values from the final iteration of the loop. But that's not the
reason for your unexpected results.

In your INTO clause, you are missing the colon (:) markers
that are needed when variables or parameters are referred to in a DSQL
statement. I seem to recall that they are optional in v.2.5 (not
sure) but that would not be a reason for me to omit them.

Your problem is your variables. With both local variables and
parameters that you are using like variables, you need to

(1) Initialise your variables before starting the loop (they start out
as NULL)
and
(2) Re-initialise them at the end of the loop (otherwise, until the
next time the loop gets a "hit", the variables retain the values that
existed after the last "hit").

Also, once you fix those problems, I see no point in adding extra cost
by revisiting the PER_ADDRESS table to verify the non-existence of
the records you just deleted. If the delete had failed, you would
already be in an exception condition and would have jumped past that
block to the last END statement.

You don't actually need ADDRESS1 in your working set, either, since
you don't do anything with it.

With the correct initialisation/re-initialisation, you already have
the value of ADDR_ID at that point: either a genuine ID (if the
current ADDRESS format matches the condition) or the dummy value from the
initialisation (if no invalid addresses were found for that ADDR_ID in
PER_ADDRESS).

CREATE PROCEDURE P_CLEAN_ADDR
AS
DECLARE ADDR_ID Integer = -1;
/* DECLARE ADDRESS VarChar(50) = ''; not needed */
DECLARE PER_ADDR_CNT SmallInt = -1;

begin
FOR SELECT A.ADDR_ID,
/* A.ADDRESS1, not needed */
(SELECT COUNT(*) FROM PER_ADDRESS PA
WHERE PA.ADDR_ID = A.ADDR_ID) AS PER_ADDR_CNT

FROM ADDRESS A
WHERE ADDRESS1 STARTING WITH '0 '
INTO :ADDR_ID, /* :ADDRESS, */ :PER_ADDR_CNT
DO
BEGIN
IF (PER_ADDR_CNT > 0) THEN
begin
DELETE FROM PER_ADDRESS WHERE ADDR_ID = :ADDR_ID;
DELETE FROM ADDRESS WHERE ADDR_ID = :ADDR_ID;
end
-- re-initialise variables
ADDR_ID = -1;
/* ADDRESS = ''; */
PER_ADDR_CNT = -1;
END
end ^^

hth,
Helen