CASE Help - 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 2005 Forums

Transact-SQL (2005)

CASE Help

Author

Topic

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2008-03-05 : 15:13:45

I'm trying to have a simple case, when my variable has an asterix in it, do something, otherwise, do something else..


declare @var as varchar(400)
set @var = '4.00*3'
--set @var = '4.3'
select
    case
        when @var like '%*%' 
            then cast(cast(substring(@var, vmfg.dbo.fninstr(0, @var, '*')+1, len(cast( @var as varchar(5000)))) as decimal(15,8))*cast(substring(@var, 0, vmfg.dbo.fninstr(0, @var, '*')-1) as decimal(15,8)) as decimal(15,8))
        else @var 
        end as V

V
========
12.00000000

above output is what I expected, now switch var


declare @var as varchar(400)
--set @var = '4.00*3'
set @var = '4.3'
select
    case
        when @var like '%*%' 
            then cast(cast(substring(@var, vmfg.dbo.fninstr(0, @var, '*')+1, len(cast( @var as varchar(5000)))) as decimal(15,8))*cast(substring(@var, 0, vmfg.dbo.fninstr(0, @var, '*')-1) as decimal(15,8)) as decimal(15,8))
        else @var 
        end as V

When I run it with var as 4.3, I get an error.. the error makes sense because running 4.3 through the first WHEN should error.. but why does the case statement even run my variable through the first case statement?

Simplify the first WHEN to just a varchar, and it works..


declare @var as varchar(400)
--set @var = '4.00*3'
set @var = '4.3'
select
    case
        when @var like '%*%'  
            then 'wtf'
            --then cast(cast(substring(@var, vmfg.dbo.fninstr(0, @var, '*')+1, len(cast( @var as varchar(5000)))) as decimal(15,8))*cast(substring(@var, 0, vmfg.dbo.fninstr(0, @var, '*')-1) as decimal(15,8)) as decimal(15,8))
        else @var 
        end as V

V
=====
4.3

Why should changing the first WHEN prevent the error? I would expect a case statement to skip the first when's THEN since the expression doesn't return true. What gives?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-03-05 : 16:26:42

Is your Goal to parse out the astrix and return a DECIMAL?

Your case statement returns two different types, so that's a problem. I'd change it to parse out eh astrix then cast the entire CASE statement to a DECIMAL. Make sense?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-05 : 16:42:15

quote:

4.3, I get an error.. the error makes sense because running 4.3 through the first WHEN should error..

It shouldn't error, it would simply move on to the next WHEN, or ELSE if it does not meet the when condition.


declare @var as varchar(400)
declare @varpos as int
set @var = '4.00*3'

SET @varpos = charindex('*',@var)
Print @varpos


select case when @varpos > 0 
          then cast(left(@var,@varpos-1) as decimal(12,6)) * cast(right(@var,len(@var)-@varpos) as decimal(12,6))   
        else @var 
        end 
----
set @var = '4.3'
SET @varpos = charindex('*',@var)
select case when @varpos > 0 
          then cast(left(@var,@varpos-1) as decimal(12,6)) * cast(right(@var,len(@var)-@varpos) as decimal(12,6))   
        else @var 
        end

seems cleaner to me...but there are probably better ways.

results of above in order:


12.00000

4.3

Poor planning on your part does not constitute an emergency on my part.

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2008-03-05 : 16:51:40

quote:
Originally posted by dataguru1971

It shouldn't error, it would simply move on to the next WHEN, or ELSE if it does not meet the when condition.

Yes is strange, I can comment out the THEN, and write in the simple char value and it does not error.. I've even tried flipping the WHEN around using a not like.. same thing.

quote:


declare @var as varchar(400)
declare @varpos as int
set @var = '4.00*3'

SET @varpos = charindex('*',@var)
Print @varpos


select case when @varpos > 0 
          then cast(left(@var,@varpos-1) as decimal(12,6)) * cast(right(@var,len(@var)-@varpos) as decimal(12,6))   
        else @var 
        end 
----
set @var = '4.3'
SET @varpos = charindex('*',@var)
select case when @varpos > 0 
          then cast(left(@var,@varpos-1) as decimal(12,6)) * cast(right(@var,len(@var)-@varpos) as decimal(12,6))   
        else @var 
        end

seems cleaner to me...but there are probably better ways.

results of above in order:


12.00000

4.3

Poor planning on your part does not constitute an emergency on my part.

Yes it does look better but I'm building an update statement actually.

I started another thread a bit ago, if I can get answer in there then that will solve this problem.

Kbalz
Yak Posting Veteran

94 Posts

Posted - 2008-03-05 : 16:55:31

quote:
Originally posted by Lamprey

Is your Goal to parse out the astrix and return a DECIMAL?

Your case statement returns two different types, so that's a problem. I'd change it to parse out eh astrix then cast the entire CASE statement to a DECIMAL. Make sense?

You are right, and I did need a decimal to come out of the case no matter what.



declare @var as varchar(400)
set @var = '4.00*3'
--set @var = '4.3'
select
    case
        when @var like '%*%' 
            then cast(cast(substring(@var, vmfg.dbo.fninstr(0, @var, '*')+1, len(cast( @var as varchar(5000)))) as decimal(15,8))*cast(substring(@var, 0, vmfg.dbo.fninstr(0, @var, '*')-1) as decimal(15,8)) as decimal(15,8))
        else cast(@var as decimal(15,8))
        end as V

Works great.

My goal was that if the varchar was an area calculation, then multiply the forumula out.. if it was linear, than pass the varchar as decimal. My other thread builds on the same problem however. Anyways this works now thanks.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-05 : 17:00:22

[code]
cast(cast(substring(@var, vmfg.dbo.fninstr(0, @var, '*')+1,
len(cast( @var as varchar(5000)))) as decimal(15,8))*
cast(substring(@var, 0, vmfg.dbo.fninstr(0, @var, '*')-1) as decimal(15,8)) as decimal(15,8))
[/code]

is better than [code]cast(left(@var,@varpos-1) as decimal(12,6)) * cast(right(@var,len(@var)-@varpos) as decimal(12,6)) [/code] ?

It just seems that my option is easier to manage.... glad you got it working..

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources