SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 CASE Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kbalz
Yak Posting Veteran

USA
94 Posts

Posted - 03/05/2008 :  15:13:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

4604 Posts

Posted - 03/05/2008 :  16:26:42  Show Profile  Reply with Quote
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?
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 03/05/2008 :  16:42:15  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote
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.


Edited by - dataguru1971 on 03/05/2008 16:42:38
Go to Top of Page

Kbalz
Yak Posting Veteran

USA
94 Posts

Posted - 03/05/2008 :  16:51:40  Show Profile  Reply with Quote
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.
Go to Top of Page

Kbalz
Yak Posting Veteran

USA
94 Posts

Posted - 03/05/2008 :  16:55:31  Show Profile  Reply with Quote
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.
Go to Top of Page

dataguru1971
Flowing Fount of Yak Knowledge

USA
1464 Posts

Posted - 03/05/2008 :  17:00:22  Show Profile  Send dataguru1971 an AOL message  Send dataguru1971 a Yahoo! Message  Reply with Quote

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


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

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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000