CASE Expression syntax - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

CASE Expression syntax

Author

Topic

patshaw
Posting Yak Master

177 Posts

Posted - 2006-11-07 : 04:26:54

Can someone advise me on how I could perform the following CASE statement.

SELECT
(case when Cond1>0 AND Cond2=0 AND Cond3=0 then 1 else 0 end) as Result,
(case when Cond1>0 AND Cond2>0 AND Cond3=0 then 1 else 0 end) as Result,
(case when Cond1>0 AND Cond2=0 AND Cond3>0 then 1 else 0 end) as Result

I have tried to include OR's but cannot get the syntax right.

Kristen
Test

22859 Posts

Posted - 2006-11-07 : 04:37:22

What are you looking for as the output?

You should be getting 3 columns or 1 or 0 depending on the criteria you have stated.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 04:37:58

SELECT --SomeCol,
SUM(case when Cond1>0 AND Cond2=0 AND Cond3=0 then 1 else 0 end) as Result1,
SUM(case when Cond1>0 AND Cond2>0 AND Cond3=0 then 1 else 0 end) as Result2,
SUM(case when Cond1>0 AND Cond2=0 AND Cond3>0 then 1 else 0 end) as Result3
FROM Sometable
--GROUP BY SomeCol

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-11-07 : 04:40:14

You're guessing Peso ...

[later] I've just read your mind ...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 04:44:54

Yes I am! These question are fun because the OP provide so little information. It makes it impossible to answer correctly!
However it is a great opportunity to improve my mindreading skills...

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 04:46:43

SELECT * FROM MyTable
WHERE
(case when Cond1>0 AND Cond2=0 AND Cond3=0 then 1 else 0 end) > 0
OR
(case when Cond1>0 AND Cond2>0 AND Cond3=0 then 1 else 0 end) > 0
OR
(case when Cond1>0 AND Cond2=0 AND Cond3>0 then 1 else 0 end) > 0

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-11-07 : 04:49:34

Now you're hedging ....

that's the same as

SELECT * FROM MyTable
WHERE
(Cond1>0 AND Cond2=0 AND Cond3=0)
OR
(Cond1>0 AND Cond2>0 AND Cond3=0)
OR
(Cond1>0 AND Cond2=0 AND Cond3>0)

patshaw
Posting Yak Master

177 Posts

Posted - 2006-11-07 : 04:49:51

I want to return 1 if either of the conditions match:
If
Cond1>0 AND Cond2=0 AND Cond3=0 then Result = 1
OR
Cond1>0 AND Cond2>0 AND Cond3=0 then Result = 1
OR
Cond1>0 AND Cond2=0 AND Cond3>0 then Result = 1
ELSE
(None of the above apply) Result = 0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 04:53:00

[code]IF Cond1 > 0 AND Cond2 = 0 AND Cond3 = 0
OR Cond1 > 0 AND Cond2 > 0 AND Cond3 = 0
OR Cond1 > 0 AND Cond2 = 0 AND Cond3 > 0
SET Result = 1
ELSE
SET Result = 0[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 04:58:46

Or much shorter and simplified

IF Cond1 > 0 AND (SIGN(Cond2) & SIGN(Cond3)) = 0
	SET @Result = 1
ELSE
	SET @Result = 0

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-11-07 : 05:08:24

You need some brackets in there Peso:


IF	(Cond1 > 0 AND Cond2 = 0 AND Cond3 = 0)
	OR (Cond1 > 0 AND Cond2 > 0 AND Cond3 = 0)
	OR (Cond1 > 0 AND Cond2 = 0 AND Cond3 > 0)
	SET Result = 1
ELSE
	SET Result = 0

Kristen

Kristen
Test

22859 Posts

Posted - 2006-11-07 : 05:13:04

Errmmm ... I think you meant a bitwise OR Peso


IF Cond1 > 0 AND (SIGN(Cond2) | SIGN(Cond3)) = 0
	SET @Result = 1
ELSE
	SET @Result = 0

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 05:20:23

No, I do not need () since AND has higher priority than OR.
No, I need a bitwise AND in the optimized query beacuse Cond2 and Cond3 can not be > 0 simultaneusly.
One can, but then the other one has to be zero.

Zero & Zero         equals 0
Zero & Positive     equals 0
Positive & Zero     equals 0
Positive & Positive equals 1

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-11-07 : 05:41:30

Two way-to-subtle side effects for my liking - we avoid coding that way because future maintenance changes are more likely to introduce errors.

I misread the criteria which you are using SIGN for as "any of then >0" rather than the mutually exclusive - another reason, IMHO, to code it more read-ably - even at the expense of performance - and if that is not acceptable then such subtle effects need a decently descriptive comment.

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 05:46:55

I agree with readability.
It would be fun to compare the two queries in a production environment. To see if three IFs is faster than bitwise AND for SIGN.

Peter Larsson
Helsingborg, Sweden

Kristen
Test

22859 Posts

Posted - 2006-11-07 : 06:40:22

I did ponder whether the SIGN function was slower/faster than "X > 0" ... but I didn't ponder it long enough to actually test it!

Kristen

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2006-11-07 : 12:43:31

Couldn't all of this be simplified to


IF Cond1 + Cond2 + Cond3 > 0
	SET @Result = 1
ELSE
	SET @Result = 0

or am I missing something here?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 12:56:36

Yes you are, unfortenately.
Cond2 and Cond3 must not be both positive.
They can both be zero, but not both positive.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources