| Author |
 Topic  |
|
|
Kbalz
Yak Posting Veteran
USA
94 Posts |
 Posted - 03/05/2008 : 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
Flowing Fount of Yak Knowledge
3821 Posts |
Posted - 03/05/2008 : 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
Flowing Fount of Yak Knowledge
USA
1464 Posts |
Posted - 03/05/2008 : 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.
|
Edited by - dataguru1971 on 03/05/2008 16:42:38 |
|
|
|
Kbalz
Yak Posting Veteran
USA
94 Posts |
Posted - 03/05/2008 : 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
USA
94 Posts |
Posted - 03/05/2008 : 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
Flowing Fount of Yak Knowledge
USA
1464 Posts |
Posted - 03/05/2008 : 17:00:22
|
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.
|
|
|
| |
Topic |
|
|
|
CASE Help
