use calculated field in case statement

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)

use calculated field in case statement

Author

Topic

jn-at-uk
Starting Member

20 Posts

Posted - 2005-05-17 : 09:51:08

Hi,
I am building a select string and want to use a calculated a field.
Except ,my calculation looks tacky if i use one long statement.
When i declare & calculate @CntSession & @CntSales, i get an error but when i
do it the long way like the instead of section it works.

I want to do something like this :

select @sql1 = 'Select '
Select @sql1 = @sql1 + 'count(sessionid) as [Visits],'

Select @sql1 = @sql1 + 'case when isnumeric(result) = 1
then
select @CntSession = count(cast(Sessionid as numeric))
select @CntSales = count(cast(result as decimal))
convert(decimal(18,2),(1.0)* @CntSession/@CntSales) else 0.00 end [% Conv],'

instead of

Select @sql1 = @sql1 + 'case when isnumeric(result) = 1
then
convert(decimal(18,2),(1.0)* count(cast(Sessionid as numeric))/count(cast(result as decimal))) else 0.00 end [% Conv],'

any ideas

Kristen
Test

22859 Posts

Posted - 2005-05-19 : 06:53:03

I reckon you are making that job 10 times harder by doing it as a created string, to then execute. During development I'd just create the appropriate SQL syntax, until it works, then convert it into a string to be executed.

So something like:


SELECT 
@xxxVisits = count(sessionid),
@CntSession = case when isnumeric(result) = 1 
then count(cast(Sessionid as numeric)) ELSE NULL END,
@CntSales =  case when isnumeric(result) = 1 
then  count(cast(result as decimal)) ELSE NULL END,
@xxxConv = case when isnumeric(result) = 1 
then convert(decimal(18,2),(1.0)* @CntSession/@CntSales) else 0.00 end

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources