# Thread: Simple Query causing strange problem.

1. Registered User
Join Date
Sep 2003
Location
Mumbai, India
Posts
36

## Unanswered: Simple Query causing strange problem.

declare @l decimal(38,2)
select @l = 24.35

if @l - convert(int,@l) = 0
select floor (@l)
else
select @l

select case
when @l - convert(int, @l) = 0 then floor (@l)
else @l
end

The if statement is giving correct result, but the case statement is not. I am fed up why it is so. Please advise.

2. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,863
Weird! And this gives the right result:

declare @l decimal(38,2)
select @l = 24.35

if @l - convert(int,@l) = 0
select floor (@l)
else
select @l

select case when @l - convert(int, @l) = 0 then 2 /*floor (@l)*/ else @l end, @l

3. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,863
Got it. The column is being converted to an integer, because the first possible value is an integer. Maybe because any value can be an integer. Not sure, there.

declare @l decimal(38,2)
select @l = 24.35

if @l - convert(int,@l) = 0
select floor (@l)
else
select @l

select case when @l - convert(int, @l) = 0 then convert(decimal (38, 2), floor (@l)) else @l end, @l

4. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Yeah...bizzare

I thought htis would have worked

Code:
```SELECT CASE WHEN @l - CONVERT(decimal(38,2),FLOOR(@l)) = 0 then floor(@l)
else @l
END```
But it doesn't

If you change to float it works...but float is quirky...

5. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Originally posted by MCrowley
Got it. The column is being converted to an integer, because the first possible value is an integer. Maybe because any value can be an integer. Not sure, there.

declare @l decimal(38,2)
select @l = 24.35

if @l - convert(int,@l) = 0
select floor (@l)
else
select @l

select case when @l - convert(int, @l) = 0 then convert(decimal (38, 2), floor (@l)) else @l end, @l
WHO DA MAN!

(who da scrub)

6. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,863
Bah! I just looked at the query plan to see what the heck was going on. I saw one too many converts going on.

7. Registered User
Join Date
Sep 2003
Location
Mumbai, India
Posts
36
Thanks Guys. My application requires that when @l is like xx.00, then I should return xx only. When the variable is like xx.yy, then I should return xx.yy. Now the same works with if. But it doesn't work with case.
If @l = 24.00, my rsult should be 24, if @l=24.35 , my result needs to be 24.35. But (@l - convert (int, @l) is behaving differently within an if statement and a case statement.

declare @l decimal(38,2)
select @l = 24.35

if @l - convert(int,@l) = 0
select floor (@l)
else
select @l

select case
when @l - convert(int, @l) = 0 then floor (@l)
else @l
end

8. Registered User
Join Date
Jan 2003
Location
Massachusetts
Posts
5,863
I think a column in a result set can only be of one data type. For example:

create table test1
(col1 decimal(38, 2))
go
insert into test1 values (24.35)
insert into test1 values (13.86)

create table test2
(col1 int)
go
insert into test1 values (25)
insert into test1 values (14)
go
select *
from test1
union all
select *
from test2

You may have a lot better luck going at this problem from the application (presentation) layer. Otherwise, the only way I can think of doing this in one select statement is to convert everything to character data. This will cause no end of problems for sorting, and the client will have to re-display the data, anyway. Not to mention the increased size of the resultsets going from the DB server to the client.

9. Registered User
Join Date
Sep 2003
Location
Mumbai, India
Posts
36
I have a new scoop to add to this. WHile trying out options, I found this:

declare @l decimal(38,2)
select @l = 24.35

select case
when @l <> convert(int, @l) then convert(decimal (38, 0), @l)
else @l
end

declare @l decimal(38,2)
select @l = 24.35

select case
when @l = convert(int, @l) then convert(decimal (38, 0), @l)
else @l
end

These two statements are giving the same result. There is no effect of changing <> to = within the case statement. I wonder.

10. Registered User
Join Date
Sep 2003
Location
Mumbai, India
Posts
36
Finally, after much permutations and combinations, the following statement is fulfilling the requirement:

declare @l decimal(38,2)
select @l = 24.35

select case
when convert(int,@l*100 - floor(@l)*100) = 0 then substring(convert(varchar,@l),1 , datalength(@l)-3)
else convert(varchar,@l)
end

11. Registered User
Join Date
Sep 2003
Location
Mumbai, India
Posts
36
I am unable to comprehend why other combinations are not working. Now that something has finally worked out, albeit illogically, I can move ahead with the application. Thanks guys. --Suresh.

12. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
McCrowley was on the right track regarding a scalar select statement only returning one data type.

Your first method uses two different select statements, so in one case it is able to return decimal(38,2) and in the other it can return int.

Your second method uses a single select statement, which chooses to return an int value because that is the datatype returned by the first WHEN clause.

If you are trying to format your data, then you will need to cast it as a varchar anyway because assigning the result of your CASE statement to some other numeric variable just recast it as that variable type.

This, for example, works fine and is simpler than your final solution:

select case
when @l - convert(int, @l) = 0 then cast(floor (@l) as varchar(50))
else cast(@l as varchar(50))
end

...because the datatype of the return value is consistent, and is able to hold values of format ## as well as ##.##

Who do blindman?

13. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Originally posted by blindman
Who do blindman?

I don't know....a hooker?

Sorry...

Very good info though

YO, WHO DAT BLIND DUDE...HE GOT IT HAPPENIN

14. World Class Flame Warrior
Join Date
Jun 2003
Location
Ohio
Posts
12,595
What I meant to say was:

Who duh blindman? D'oh!

...and that was a real tricky question he posted! It would make a good quiz question.

15. Window Washer
Join Date
Nov 2002
Location
Jersey
Posts
10,322
Ya know...that's a good idea...

I'm going start a list...

How was she?

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•