Greetings Helen,

You're the best. Unbelievable amount of valuable information packed into
your reply. I really appreciate the time you took in providing a detailed
reply, as I have learned much from you.

I threw this SP together quickly and actually had just a SUSPEND statement
in this to begin with before I removed it and added all the other stuff,
thus the reason for the return parameters. My intent was to remove them and
have just a an iAddrID variable. That's what I get for getting frustrated
and throwing my SP as it was at the present out there. ;>)

That said, I'll go through your reply again and then see if I can get this
to work.

Thanks again,
Mike



-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]
Sent: Wednesday, May 11, 2016 11:51 PM
To: 'stwizard' stwizard@... [firebird-support]
Subject: Re: [firebird-support] Why does "IF (NOT(EXISTS(SELECT 1..." not
work as expected?

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







------------------------------------

------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Documentation item on the
main (top) menu. Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at
http://www.ibphoenix.com/resources/documents/

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
------------------------------------

Yahoo Groups Links