Sorry again, your query should not contain doubly negated exists (finally, my brain awoke), the last should simply be exists:

Delete from Table_C C
where C.dt_date <= '03/31/2007'
And not exists(
select 1 from Table_A A
where A.var_Empcode = C.var_EmpCode and
(A.var_Status = 'I'
or A.var_Resigned_But_Not_Settled = 'Y')
and exists(
select 1 from Table_B B
where A.var_EmpCode = B.var_EmpCode
And A.var_PGCode = B.var_PGCode
and B.dt_AppliedDate >= '03/31/2007'));

Sorry for messing this up,
Set

-----Original Message-----
From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Svein Erling Tysvær
Sent: 10. september 2009 14:31
To: firebird-support@yahoogroups.com
Subject: RE: [firebird-support] Speed optimization required for DELETE query

When did my logic stop working? True and not true has never been true. Let me try again:

EmpCode002: EmpCode003:
True and True and
not exists (True and not exists(True and
not exists (False)) not exists(True))
=
True and True and
not exists(True and not exists(True and
True) False)
=
True and not exists True and exists
=
Deleted Not deleted

I think this is how it works, although I find it much easier to write in SQL than the pseudocode I wrote above...

Set

-----Original Message-----
From: Svein Erling Tysvær
Sent: 10. september 2009 14:15
To: 'firebird-support@yahoogroups.com'
Subject: RE: [firebird-support] Speed optimization required for DELETE query

I cannot answer for Helen's query (I'm not used to max without group by and was surprised that it didn't produce a syntax error), but here's a walkthrough of mine for EmpCode002 and EmpCode003:

Delete from Table_C C
where C.dt_date <= '03/31/2007'

/*Is the date early enough? True or both EmpCode002 and EmpCode003 */

And not exists(
select 1 from Table_A A
where A.var_Empcode = C.var_EmpCode and
(A.var_Status = 'I'
or A.var_Resigned_But_Not_Settled = 'Y')

/*Do they exist with correct values in Table_A? True for both EmpCode002 and EmpCode003 */

and not exists( select 1 from Table_B B where A.var_EmpCode = B.var_EmpCode And A.var_PGCode = B.var_PGCode and B.dt_AppliedDate >= '03/31/2007' ));

/*Do they have a recent AppliedDate? True for EmpCode003, false for EmpCode002*/

So,

EmpCode002: EmpCode003:
True and True and
not (True and not (True and
not (False)) not (True))
=
True and True and
not (True and not (True and
True) False)
=
True and True and
not True not False
=
True False
=
Deleted Not deleted

Set


------------------------------------

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Visit http://www.firebirdsql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoenix.com

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yahoo! Groups Links