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' stwiz...@att.net [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
