Please disregard, as I have figured this out.

Thanks,
Mike

----- Original Message -----
From: SoftTech
To: firebird-support@yahoogroups.com
Sent: Tuesday, December 14, 2010 11:14 AM
Subject: {Disarmed} [firebird-support] Why does the NOT EXISTS work as expected in my SQL statement?



Greetings All,

Thanks to all that have helped me out in the past as I really appreciate all
those who reply.

Firebird 1.5

The SQL's below have been pruned way down from the original SQL's, but fully
demonstrate the issue I'm having.

I have a debtor (Person) who has 3 accounts of which only one has a judgment

62770 (Has a Judgment)
149510 and 171162 (Do not have a judgment)

This SQL returns all three records and should return only 149510 and 171162:

SELECT DISTINCT AC.ACCT_ID,
P.PERSON_ID,
COALESCE(P.FIRST_NAME,'') || COALESCE(' ' || P.MIDDLE_NAME
|| ' ', ' ') || P.LAST_NAME AS DEBTOR_NAME,
P.SOC_SEC_NO
FROM DEBTOR_CASE_DEBT DCD
JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
AND AC.CASE_ID = DCD.CASE_ID
JOIN PERSON P ON P.PERSON_ID = DCD.PERSON_ID
WHERE DCD.ACCT_ID IN (62770,149510,171162)

AND NOT (EXISTS(SELECT 1
FROM ACCT_CASE_COURT ACC
JOIN ACCT_CASE_COURT_JUDGMENT ACCJ ON
ACCJ.ACCT_CASE_COURT_ID = ACC.ACCT_CASE_COURT_ID
JOIN ACCT_CASE_COURT_PERSON ACCP ON
ACCP.ACCT_CASE_COURT_ID = ACC.ACCT_CASE_COURT_ID
WHERE ACC.ACCT_ID = DCD.ACCT_ID
AND ACC.CASE_ID = DCD.CASE_ID
AND ACCJ.JUDGMENT_STATUS_CODE = 'A'
AND ACCJ.JUDGMENT_FILE_STAMPED_DATE IS NOT
NULL
AND ACCP.PERSON_ID = DCD.PERSON_ID ))

AND ((P.SOC_SEC_NO LIKE '___-__-____') AND P.SOC_SEC_NO NOT LIKE
'000%')

This SQL correctly return the only account (62770) that actually has a
judgment:

SELECT DISTINCT AC.ACCT_ID,
P.PERSON_ID,
COALESCE(P.FIRST_NAME,'') || COALESCE(' ' || P.MIDDLE_NAME
|| ' ', ' ') || P.LAST_NAME AS DEBTOR_NAME,
P.SOC_SEC_NO
FROM DEBTOR_CASE_DEBT DCD
JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
AND AC.CASE_ID = DCD.CASE_ID
JOIN PERSON P ON P.PERSON_ID = DCD.PERSON_ID
WHERE DCD.ACCT_ID IN (62770,149510,171162)

AND (EXISTS(SELECT 1
FROM ACCT_CASE_COURT ACC
JOIN ACCT_CASE_COURT_JUDGMENT ACCJ ON
ACCJ.ACCT_CASE_COURT_ID = ACC.ACCT_CASE_COURT_ID
JOIN ACCT_CASE_COURT_PERSON ACCP ON
ACCP.ACCT_CASE_COURT_ID = ACC.ACCT_CASE_COURT_ID
WHERE ACC.ACCT_ID = DCD.ACCT_ID
AND ACC.CASE_ID = DCD.CASE_ID
AND ACCJ.JUDGMENT_STATUS_CODE = 'A'
AND ACCJ.JUDGMENT_FILE_STAMPED_DATE IS NOT
NULL
AND ACCP.PERSON_ID = DCD.PERSON_ID ))

AND ((P.SOC_SEC_NO LIKE '___-__-____') AND P.SOC_SEC_NO NOT LIKE
'000%')

So why does the EXISTS works as expected and the NOT EXIST does not work as
it should?

Any tips appreciated.

Thanks,
Mike




--
MailScanner Virus/Spam/Malware: PASS (GZ)

[Non-text portions of this message have been removed]