|Subject||Why does "IF (NOT(EXISTS(SELECT 1..." not work as expected?|
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 form the ADDRESS table.
What might I be doing wrong?
SET TERM ^^ ;
CREATE PROCEDURE P_CLEAN_ADDR returns (
FOR SELECT A.ADDR_ID,
(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
IF (PER_ADDR_CNT > 0) THEN
DELETE FROM PER_ADDRESS PA WHERE PA.ADDR_ID = :ADDR_ID;
IF (NOT(EXISTS(SELECT 1
WHERE ADDR_ID = :ADDR_ID))) THEN
DELETE FROM ADDRESS WHERE ADDR_ID = :ADDR_ID;
SET TERM ; ^^