May 23, 2008 at 9:28 am
Hello,
I have a query where I am trying to specifically exclude records where the combination of two conditions is true. When I type it it looks like this:
SELECT
fldA, fldB, fldC
FROM
vwMyView
WHERE
(fldTimestamp >= @timeval)
AND
NOT ((location_ID = 2) AND (removed_ID = 1))
The idea here is that records where location_ID = 2 but removed_ID <> 1 will be returned, as will records where removed_ID = 1 but location_ID <>2. I want it so that only records where location = 2 and removed_ID = 1 will be excluded. When I run the query, the query is reformated/refactored to this:
SELECT
fldA, fldB, fldC
FROM
vwMyView
WHERE
(fldTimestamp >= @timeval) AND (NOT(location_ID = 2)) OR
(fldTimestamp >= @timeval) AND (NOT(removed_ID = 1))
I suspect that I have my brackets wrong in the original query. Could someone help me out? Thanks,
Tom
May 23, 2008 at 9:57 am
The query editor sometimes refactors things in a way that "makes sense" to it. From the looks of it - both statements are logically the same, so I don't see any harm in using either.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 23, 2008 at 9:59 am
If you had this data, what would you want returned? Assuming all 3 dates are greater than or equal to your date criteria?
fldTimestamp location_id removed_id
----------------------- ----------- -----------
2008-05-24 12:00:37.710 1 1
2008-05-25 12:00:37.710 1 2
2008-05-26 12:00:37.710 2 1
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 23, 2008 at 10:10 am
Hi and thanks to both of you for your replies. I have seen the 'auto refactoring' thing before and was under the impression that it didn't change the meaning of the original command. However, I suspected that maybe what I wanted my original to say and what it was actually saying might be two different things! 😉
Jack, from your sample data I would want the first two records to be returned. Either of the conditions on their own should be fine. It's only the combination of the two conditions (where location = 2 AND removed = 1) that I want to exclude. Hope that makes sense!
So just to re-frame the question, do the two queries in my original question mean the same thing? And if so, are they doing what I wanted (as explained above)?
May 23, 2008 at 11:16 am
thomas.lemesurier (5/23/2008)
Hi and thanks to both of you for your replies. I have seen the 'auto refactoring' thing before and was under the impression that it didn't change the meaning of the original command. However, I suspected that maybe what I wanted my original to say and what it was actually saying might be two different things! 😉Jack, from your sample data I would want the first two records to be returned. Either of the conditions on their own should be fine. It's only the combination of the two conditions (where location = 2 AND removed = 1) that I want to exclude. Hope that makes sense!
So just to re-frame the question, do the two queries in my original question mean the same thing? And if so, are they doing what I wanted (as explained above)?
You're looking at a NAND, in which case both notations are correct:
a NAND b = NOT (a AND b) = (NOT a) OR (NOT b)
In your case - with a third criterion:
c AND (a NAND b) = c AND ((NOT a) OR (NOT b)) = (c AND (NOT a)) OR (c AND (NOT b))
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 23, 2008 at 11:44 am
Based on the following test both queries are evaluated the same and return the same and desired data:
[font="Courier New"]
CREATE TABLE #vwMyView (fldTimestamp DATETIME, location_id INT, removed_id INT)
CREATE INDEX IX_Test ON #vwMyView(fldTImestamp, location_id, removed_id)
INSERT INTO #vwMyView
SELECT
GETDATE()+1,
1,
1
UNION ALL
SELECT
GETDATE()+2,
1,
2
UNION ALL
SELECT
GETDATE()+3,
2,
1
DECLARE @timeval DATETIME
SET @timeval = GETDATE()
SELECT
fldTimestamp,
location_id,
removed_id
FROM
#vwMyView
WHERE
fldTimestamp >= @timeval AND
NOT (location_ID = 2 AND removed_ID = 1)
SELECT
fldTimestamp,
location_id,
removed_id
FROM
#vwMyView
WHERE
(fldTimestamp >= @timeval) AND (NOT(location_ID = 2)) OR
(fldTimestamp >= @timeval) AND (NOT(removed_ID = 1))
[/font]
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 27, 2008 at 2:55 am
Matt, Jack,
Thanks, you've proved this to me conclusively, nice work 😎
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply