Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    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. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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. #4
    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...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Cool

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

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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. #7
    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.
    Please advise.

    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. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    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. #9
    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. #10
    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. #11
    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. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    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
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    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?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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