CASE Statement - 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

SQL Server Development (2000)

CASE Statement

Author

Topic

majnoon
Starting Member

26 Posts

Posted - 2003-09-17 : 11:32:48

Hi:

can you nest case statement

eg:

select value = case when cond1 > 1 then case when cond2 < 10 then cond1 + cond2 else cond1 - cond2 end

If you can then I am doing something wrong, if not then i'll have to find another way

Wishing you a peaceful journey

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2003-09-17 : 11:39:43

Not sure about nesting CASE statements but you can test more than one condition

CASE WHEN cond1 > 1 and cond2 <10 THEN...

your statement edited to see if it will work:
CASE WHEN cond1 > 1 THEN (CASE WHEN cond2 < 10 THEN cond1 + cond2 ELSE cond1 - cond2 end) end

X002548
Not Just a Number

15586 Posts

Posted - 2003-09-17 : 11:41:49

Yes...

but you're missing an END...need 1 per CASE...


SELECT  @value = CASE WHEN cond1 > 1 THEN 
		      CASE WHEN cond2 < 10 THEN cond1 + cond2 
					   ELSE cond1 - cond2 
		      END
		 END

I know it's probably an example...but you could easily do it this way:



USE Northwind
GO

SELECT CASE WHEN OrderId > 10248 
	     AND OrderID < 10300 THEN 1 
				 ELSE OrderId 
       END 
  FROM Orders

Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-09-17 : 11:43:10

try indenting.


select value = case when cond1 > 1 then 
                 case when cond2 < 10 then 
                   cond1 + cond2 
                 else 
                   cond1 - cond2 
                 end

as you can see, you are missing a final "end" to end the first (outer) CASE statement. Also, you might want an "ELSE" for the first CASE statement as well.

Something like:


select value = case when cond1 > 1 then 
                 case when cond2 < 10 then 
                   cond1 + cond2 
                 else 
                   cond1 - cond2 
                 end
               else
                 cond3
               end

- Jeff

majnoon
Starting Member

26 Posts

Posted - 2003-09-17 : 11:58:54

Thanks for that, its always the dummest things that throw you off, I shall go and hang my head in shame

Wishing you a peaceful journey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources