embeded 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)

embeded Case statement

Author

Topic

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-07-24 : 19:23:00

is it possible to embed a case statement within a case statement all in a select statement

Table DDL:

Declare @Result table
(
SetId int,
ItemId decimal(20,0) identity not null,
Balance decimal(24, 2),
Rate decimal(30,20),
)
ex:
select setid, ItemId,
(select case SetId
when 2 then Balance*100.00
else
case Rate
when < .08 then Balance * 10.00
else
Balance * 37.00
end
end
),
rate
from @Result

I need to test for two cases in a select statement, if the first case passes, I need to evaluate for the second case.

Please don't ask me to redesign the query as it is one of those instances where a procedure has existed forever i am adding one new functionality to it

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-24 : 19:41:32

Try this:


select 
	setid, 
	ItemId, 
	SomeColumn =
		case 
			when setid = 2 then Balance*100.00 
			else
				case 
					when rate < .08 then Balance * 10.00 
					else Balance * 37.00 
				end
		end,
	rate 
from @Result

Tara Kizer

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-07-24 : 20:42:23

quote:

Originally posted by tkizer

Try this:


select 
	setid, 
	ItemId, 
	SomeColumn =
		case 
			when setid = 2 then Balance*100.00 
			else
				case 
					when rate < .08 then Balance * 10.00 
					else Balance * 37.00 
				end
		end,
	rate 
from @Result

Tara Kizer

Thank you so much...Glad to know I can do that..Thanks a million

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-25 : 08:19:16

I would tend to write that like this. I've no idea about performance though (but would, of course, be interested in any opinions or tests).

select 
    setid, 
    ItemId, 
    SomeColumn = Balance * 
        case
            when setid = 2 then 100.00
            when not setid = 2 and rate < .08 then 10.00
            else 37.00
        end,
    rate 
from @Result

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-25 : 09:25:14

The
not setid = 2 and
is redundant as it is taken care of by the first when (unless setid can be null)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-07-25 : 09:33:23

quote:
Originally posted by nr

The
not setid = 2 and
is redundant as it is taken care of by the first when (unless setid can be null)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Good point. So we have...

select 
    setid, 
    ItemId, 
    SomeColumn = Balance * 
        case
            when setid = 2 then 100.00
            when rate < .08 then 10.00
            else 37.00
        end,
    rate 
from @Result

Any ideas on the efficiency of this compared to the others?

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

tchinedu
Yak Posting Veteran

71 Posts

Posted - 2006-07-25 : 10:58:38

Would it make any difference if i move the logic to a Function?

quote:
Originally posted by RyanRandall

quote:
Originally posted by nr

The
not setid = 2 and
is redundant as it is taken care of by the first when (unless setid can be null)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Good point. So we have...

select
setid,
ItemId,
SomeColumn = Balance *
case
when setid = 2 then 100.00
when rate < .08 then 10.00
else 37.00
end,
rate
from @Result
Any ideas on the efficiency of this compared to the others?

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

nr
SQLTeam MVY

12543 Posts

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

Depends on how you are going to use it but often functions are slow.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources