January 17, 2013 at 3:25 pm
Concerning the puzzle below, need a SQL guru to tell me how they solved this problem.
Goal:
To get statement 2 to produce the same results as statement 1 without having to string together the columns.
DECLARE @Table1 TABLE
(
Column1 int IDENTITY(1,1),
Column2 varchar(12),
Column3 varchar(12)
)
INSERT INTO @Table1 (Column2, Column3) VALUES ('UK','U')
INSERT INTO @Table1 (Column2, Column3) VALUES ('AP','U')
INSERT INTO @Table1 (Column2, Column3) VALUES ('AP','O')
INSERT INTO @Table1 (Column2, Column3) VALUES ('UK','O')
--Statement 1
select * from @Table1
where (column2 + column3 != 'APU')
--Statement 2
select * from @Table1
where (column2 != 'AP' and column3 != 'U')
January 17, 2013 at 3:30 pm
--Statement 2
select * from @Table1
where (column2 != 'AP' or column3 != 'U')
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
January 17, 2013 at 4:10 pm
EDIT: Scratch this, bad code. Sorry about that.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 17, 2013 at 4:14 pm
*facepalm* sorry about the earlier code. Try this again:
Alternatively:
SELECT
*,
CASE WHEN column2 <> 'AP' THEN 1 ELSE 0 END AS APtest,
CASE WHEN column3 <> 'U' THEN 1 ELSE 0 END AS Utest,
CASE WHEN column2 <> 'AP' AND column3 <> 'U' THEN 1 ELSE 0 END AS APUtest
FROM
@Table1
WHERE
NOT ( column2 = 'AP' AND column3 = 'U')
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
January 17, 2013 at 4:58 pm
Evil Kraig F (1/17/2013)
*facepalm* sorry about the earlier code. Try this again:Alternatively:
SELECT
*,
CASE WHEN column2 <> 'AP' THEN 1 ELSE 0 END AS APtest,
CASE WHEN column3 <> 'U' THEN 1 ELSE 0 END AS Utest,
CASE WHEN column2 <> 'AP' AND column3 <> 'U' THEN 1 ELSE 0 END AS APUtest
FROM
@Table1
WHERE
NOT ( column2 = 'AP' AND column3 = 'U')
Phil Parkin (1/17/2013)
--Statement 2
select * from @Table1
where (column2 != 'AP' or column3 != 'U')
For those not versed in boolean logic:
NOT ( column2 = 'AP' AND column3 = 'U') -->
NOT(column2 = 'AP') OR NOT(column3 = 'U') -->
column2 != 'AP' or column3 != 'U'
Which means: NOT ( column2 = 'AP' AND column3 = 'U') is equivalent to: column2 != 'AP' or column3 != 'U'.
January 22, 2013 at 10:00 am
Phil,
Thanks for the help. I had tried the or logic, but something else must have affected the results.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply