case - 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 2008 Forums

Transact-SQL (2008)

case

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-16 : 05:49:33

Hi,
Looking at the sql below, how can I remove the harcoded sql inside the case statements and replace it by using some kind of sql for referring to the table below which is tblCriteria to get the same data?

Thanks


tblMain
-------

Date		Currency	price		EffortCost
-----------------------------------------------------------
1/2/2000	ABC		1.4		2000
3/2/2010	CBA		3.2		1000
5/3/2011	USD		1.0		1500
6/7/2011	GBP		2.2		3500
...
...


select
	Date,
	case
		when currency = 'USD' then price * 10
		else price
	end as price,
	case
		when currency = 'GBP' then EffortCost/555
		else EffortCost
	end as EffortCost
from
	tblMain

---------------------------------
criteria table has the following data:

tblCriteria
-----------

FieldName	Operator	Value		Criteria
---------------------------------------------------------
Currency	=		USD		price * 10
Currency	=		GBP		EffortCost/555

Lewie
Starting Member

42 Posts

Posted - 2011-11-16 : 09:53:43

This may come close I think but tblCriteria needs to sepcify what to do for e.g. EffortCost if USD

Declare @Script varchar(max)

SELECT @Script = 'select Date,case '

SELECT @Script = @Script + ' when currency = ' + '''' + Value + '''' + ' then ' + Criteria
from tblCriteria

SELECT @Script = @Script + ' else price end as price, case '

SELECT @Script = @Script + ' when currency = ' + '''' + Value + '''' + ' then ' + Criteria
from tblCriteria

SELECT @Script = @Script + ' else EffortCost end as EffortCost FROM tblMain'
--select @Script
EXEC(@Script)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-16 : 10:54:19

I'm not sure I like the idea of dynamically generating this code, but if you continue down that path I'd suggest a "grouping" of some kind. In this case something to tell the difference between CASE expressions for Price and EffortCost. Additionally, In case expressions order is important, so if you ever plan on having more than one condition, it'd be a good idea to add some sort of ordinal column to know how to order multiple evaluations. And "FieldName"... well I'll let you search for some Celko posts on that.. ;)

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-16 : 11:01:48

quote:
Originally posted by Lamprey

I'm not sure I like the idea of dynamically generating this code

Can it be done without the synamic sql?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-16 : 11:15:19

quote:
Originally posted by arkiboys

quote:
Originally posted by Lamprey

I'm not sure I like the idea of dynamically generating this code

Can it be done without the synamic sql?

If it is possible, I'd probalby be so complex that it wouldn't be worth doing in a non-dynamic SQL way. It's not so much about dynamix SQL as it is about about creating this type of generic solution. I'm not saying they can't work, it's just I find that they usually don't end up working out so well.

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2011-11-16 : 11:32:54

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources