Greetings All,
Firebird 2.5.4
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 (
ADDR_ID Integer,
ADDRESS VarChar(50),
PER_ADDR_CNT SmallInt)
AS
begin
FOR SELECT A.ADDR_ID,
A.ADDRESS1,
(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
DELETE FROM PER_ADDRESS PA WHERE PA.ADDR_ID = :ADDR_ID;
IF (NOT(EXISTS(SELECT 1
FROM PER_ADDRESS
WHERE ADDR_ID = :ADDR_ID))) THEN
DELETE FROM ADDRESS WHERE ADDR_ID = :ADDR_ID;
END
end ^^
SET TERM ; ^^
