RE: [firebird-support] Why does "IF (NOT(EXISTS(SELECT 1..." not work as expected?
Greetings Thomas, Yes, I first have to get my tables cleaned up and then add cascading delete foreign key constraint on PER_ADDRESS referencing ADDRESS Thanks, Mike From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Thursday, May 12, 2016 12:29 AM To: firebird-support@yahoogroups.com 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 ^^ Very exhaustive and a lot of useful advices ... ;-) He also could simply have a cascading delete foreign key constraint on PER_ADDRESS referencing ADDRESS. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
RE: [firebird-support] Why does "IF (NOT(EXISTS(SELECT 1..." not work as expected?
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
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 ^^ Very exhaustive and a lot of useful advices ... ;-) He also could simply have a cascading delete foreign key constraint on PER_ADDRESS referencing ADDRESS. -- With regards, Thomas Steinmaurer http://www.upscene.com Professional Tools and Services for Firebird FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
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 <*> 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/
