June 22, 2011 at 3:47 pm
Hello everyone,
I have a little Problem here and I really need your help... I have a stored procedure and I have added this lines bellow -->
case
when ISNULL(org.is_round,1) = 1 THEN
CONVERT(NUMERIC(13,0), dc.moq)
else
CONVERT(NUMERIC(13,3), dc.moq)
end AS moq
but it doesnt work!!! I always get a 13,3 Numeric Value.
I have already tried it with this lines and that works -->
case
when ISNULL(org.is_round,1) = 1 THEN
'YES'
else
'NO'
end AS moq
I think its a sql server problem. I need this procedure to return this values exact because I am using them in a Website. Please if you know a better or smarter way to do this and when it works I would be very happy!!!!
Thank you all for your Help!!!
June 22, 2011 at 4:08 pm
Alter your clause to look like this and it should work ok, you had your when in the wrong location
case ISNULL(org.is_round,1) when 1 THEN
CONVERT(NUMERIC(13,0), dc.moq)
else
CONVERT(NUMERIC(13,3), dc.moq)
end AS moq
***The first step is always the hardest *******
June 22, 2011 at 10:59 pm
Thank you for the Answer. I tried it but it does not work. Its the same problem.
June 22, 2011 at 11:36 pm
A CASE expression can only return a single data type, so you will always get a NUMERIC(13,3).
June 23, 2011 at 12:23 am
Thanks that really helps!!! So I will focus to find another way to solve this problem!!! Thanks a lot for you help!!!
June 23, 2011 at 12:49 am
Are you sure that your evaluating expression is returning NULL?
I tried out the following and yes, I got numeric (13,0) as output.. Please have a look and let know if you are looking for something else..
declare @test-2 varchar(10)
set @test-2 = null
select case
when ISNULL(@test,1) = 1 THEN
CONVERT(NUMERIC(13,0), 10.113)
else
CONVERT(NUMERIC(13,3), 10.113)
end AS moq
-----
Outupt: when set @test-2 = null the output is 10.000
when set @test-2 = 11 the output is 10.113
June 23, 2011 at 1:00 am
Hello,
you have right. I get the right output but I need it without nulls... So I get 13.000...
In this case I need to get 13...
Thank you!
June 23, 2011 at 1:06 am
Michael Valentine Jones has right. I tried the round function but I always get the same type as result...
June 23, 2011 at 1:41 am
Pls see this link: remove trailing zeros .
You may define a function and passing your recordset into that. Again there is no such straightforward function you can use.
June 23, 2011 at 2:01 am
Wow!!! Cool Solution!
Thank you for the Help! I have figure out another solution. The values that you see are in kilogramms. We will convert the kilogramms in gramms (value * 1000) and so we solve the problem with the decimal point values!
Thanks again!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply