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 <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/