[AccessD] Query question

Raises head above parapet to see if the experts are still throwing eggs at me...

I just did a little rethinking and came to the conclusion that I was talking though my hat when I asserted 

"Where ((Condition) or (Condition) Or (Condition) or (Condition) = True)

Note that all the conditions are logically combined with the OR operation. So, if *any one* of those four conditions evaluate to true, the entire where clause is true, therefore *all* of the records will be returned."

Gibberish of course. Which leaves me back in the weird land we started in.

Question, is this SQL in a stored query, or is the "raw" sql being run?  If it's an actual query then I'd try killing it and re-writing it on the assumption that the stored query is somehow corrupt.

Lambert

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert
Sent: Thursday, June 03, 2010 9:32 AM
To: Access Developers discussion and problem solving
Cc: Kaup, Chester
Subject: Re: [AccessD] Query question

"-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Wednesday, June 02, 2010 5:08 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Query question

That's so weird. It's basically saying WHERE True = -1   or WHERE False =-1"
----------------------------

That is exactly the point. The query has a where clause. 
Lets break it down...


WHERE ((([Status]='WAGC' And [Design_Fluid_Status_Id]='WI') Or  ([Status]='WAGW' And [Design_Fluid_Status_Id]='CI') Or  ([Status]='CI' And [Design_Fluid_Status_Id]='WI') Or ([Status]='WI' 
 And [Design_Fluid_Status_Id]='CI'))=-1)


Is logically equivalent to

Where ((Condition) or (Condition) Or (Condition) or (Condition) = -1)

Which in turn is the same as

Where ((Condition) or (Condition) Or (Condition) or (Condition) = True)

Note that all the conditions are logically combined with the OR operation. So, if *any one* of those four conditions evaluate to true, the entire where clause is true, therefore *all* of the records will be returned.

Changing the where clause to

WHERE (([Status]='WAGC' And [Design_Fluid_Status_Id]='WI') Or  ([Status]='WAGW' And [Design_Fluid_Status_Id]='CI') Or  ([Status]='CI' And [Design_Fluid_Status_Id]='WI') Or ([Status]='WI' 
 And [Design_Fluid_Status_Id]='CI'))

Which is logically Where ((Condition) or (Condition) Or (Condition) or (Condition))

Or the more succinct version 

WHERE ([Status]='WAGC' And ([Design_Fluid_Status_Id]='WI') Or
 [Design_Fluid_Status_Id]='CI') Or
 ([Status]='CI' And ([Design_Fluid_Status_Id]='WI' or [Design_Fluid_Status_Id]='CI'))


Will select only the records that actually meet the conditions (apparently 4 of them fit the mold).

Hope this clarifies things a bit.

Lambert


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com