Hi,
 
plz. check the query once again u wrote
 
at the end u wrote that
 
B.dt_AppliedDate >= '03/31/2007' 
and in the begining reverse i.e.  C.dt_date <= '03/31/2007'

 
is it okay.
 
plz. check once again.
 
regards
 
Vishal Tiwari...
 
 
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' ));

--- On Thu, 10/9/09, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:


From: Svein Erling Tysvær <svein.erling.tysvaer@...>
Subject: RE: RE: [firebird-support] Speed optimization required for DELETE query
To: "firebird-support@yahoogroups.com" <firebird-support@yahoogroups.com>
Date: Thursday, 10 September, 2009, 6:03 PM


 



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.firebird sql.org and click the Resources item
on the main (top) menu. Try Knowledgebase and FAQ links !

Also search the knowledgebases at http://www.ibphoeni x.com

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

















Dream of becoming a dancing sensation? Find a dance guru on Yahoo! India Local http://in.local.yahoo.com/

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