COALESCE

  • bgodick

    SSC-Addicted

    Points: 459

    Comments posted to this topic are about the item COALESCE

  • This was removed by the editor as SPAM

  • HappyGeek

    SSCoach

    Points: 18681

    Good question, thanks

    ...

  • Ed Wagner

    SSC Guru

    Points: 286982

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Easy one, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • tom.w.brannon

    Hall of Fame

    Points: 3956

    I knew that coalesce would return the highest data type but never remember the ordering of that so I thought it was either going to be 10 or divide by zero error. I got a surprise when I checked on the value of @float:

    [font="Courier New"]print @float = 1

    select @float = .5

    [/font]

    Can somebody explain why that is?

  • Iulian -207023

    SSCertifiable

    Points: 7509

    tom.w.brannon (1/29/2016)


    I knew that coalesce would return the highest data type but never remember the ordering of that so I thought it was either going to be 10 or divide by zero error. I got a surprise when I checked on the value of @float:

    [font="Courier New"]print @float = 1

    select @float = .5

    [/font]

    Can somebody explain why that is?

    Probably it is a rounding from .5 to 1.

    What type is @float ?

    Iulian

  • sestell1

    SSChampion

    Points: 10230

    I spent way to long on this one looking for the data type precedence catch. :crazy:

  • Luis Cazares

    SSC Guru

    Points: 183633

    tom.w.brannon (1/29/2016)


    I knew that coalesce would return the highest data type but never remember the ordering of that so I thought it was either going to be 10 or divide by zero error. I got a surprise when I checked on the value of @float:

    [font="Courier New"]print @float = 1

    select @float = .5

    [/font]

    Can somebody explain why that is?

    Can you post the exact code that you're using? Float shouldn't be rounded to 1.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • DonlSimpson

    SSCertifiable

    Points: 6774

    Sneaky. When I first looked at it, I took 5,500 to be 5500 instead of two distinct arguments.

    Don Simpson



    I'm not sure about Heisenberg.

  • tom.w.brannon

    Hall of Fame

    Points: 3956

    Luis Cazares (1/29/2016)


    tom.w.brannon (1/29/2016)


    I knew that coalesce would return the highest data type but never remember the ordering of that so I thought it was either going to be 10 or divide by zero error. I got a surprise when I checked on the value of @float:

    [font="Courier New"]print @float = 1

    select @float = .5

    [/font]

    Can somebody explain why that is?

    Can you post the exact code that you're using? Float shouldn't be rounded to 1.

    Sorry, I made a mistake. Both print and select give back .5. Not sure what I was looking at.

    DECLARE @int1 INT,

    @int2 INT,

    @float FLOAT

    SET @float = COALESCE

    (@int1,

    @int2,

    .5)

    SELECT COALESCE

    (@int1,

    5,500)

    / @float

    print @float;

    select @float;

  • palotaiarpad

    SSCertifiable

    Points: 5317

    Hmm. I thought COALESCE will return the datatype of the leftmost parameter. Learnt something. Thanks.

  • Luis Cazares

    SSC Guru

    Points: 183633

    palotaiarpad (1/31/2016)


    Hmm. I thought COALESCE will return the datatype of the leftmost parameter. Learnt something. Thanks.

    ISNULL() does work that way.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Bhushan Kulkarni

    SSCrazy

    Points: 2829

    Simple one 🙂

  • patricklambin

    SSCrazy Eights

    Points: 9964

    Still a week-end question for tired persons.

    It is an easy question to reply as soon as you know that a variable declared thru the Declare statement is always initialized to a null value when it is not initialized inside the Declare or by a SET statement ( before its 1st use.

    I recognize that I have forgotten this "special" way of initialization up to a session of SQL Server Days related to the basics of T-SQL I participated for the release of SQL Server 2008 R2. As it is now anchored in my memory , the choice was easy. A prof that even a basic session can be useful for a man who began with SQL Server 6.5. Since I have used nearly all versions except the 7.0 . I have to recognize that I am using T-SQL but it is not my favorite topic.

    Anyway , thanks for this easy question which permits to dig deeply inside my old memory.

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply