COALESCE - 2

  • sreenivas.kumarreddy

    SSC Enthusiast

    Points: 143

    Comments posted to this topic are about the item COALESCE - 2

  • Lokesh Vij

    SSChampion

    Points: 10836

    Easy one for Friday 🙂

    As COALESCE always picks up the first not null value from LOV, it was easy to guess the answer.

    ~ Lokesh Vij


    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • demonfox

    SSCertifiable

    Points: 6289

    Lokesh Vij (11/8/2012)


    Easy one for Friday 🙂

    As COALESCE always picks up the first not null value from LOV, it was easy to guess the answer.

    It threw an error , didn't it ..

    because both NULL value cause an error on coalesce ..

    -- this would give PassFail

    Declare @a varchar(100)

    Declare @B varchar(100)

    select @a = isnull(coalesce(@a,coalesce(@a,null)),'Pass')+'Fail'

    select @a

    -- where as this would give an error

    Declare @a varchar(100)

    Declare @B varchar(100)

    select @a = isnull(coalesce(@a,coalesce(null,null)),'Pass')+'Fail'

    select @a

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Good one. 🙂

    Even though knowing the answer (and I said it out loud) ....I made myself to execute the query to confirm.... :w00t: :crazy:

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Koen Verbeeck

    SSC Guru

    Points: 258941

    Great question to close the week, thanks!

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

  • kapil_kk

    SSC-Insane

    Points: 21316

    GOOD QUESTION WITH +1 AS MOVING TOWARDS END OF THE WEEK :-):-)

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • nghiavt

    SSC Enthusiast

    Points: 142

    another thing to study, coalesce(null,null) ==> error.

  • Stuart Davies

    SSCoach

    Points: 18874

    Good question - thanks. Will now end the week on a high 😀

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a questionThere are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan I would never join a club that would allow me as a member - Groucho Marx

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    Good question, but wrong explanation (and also wrong error message from SQL Server):

    The coalesce expects at least one typed NULL in order to execute and hence the error.

    Simple example that does NOT contain a typed NULL:

    COALESCE(1,1)

    Best Regards,

    Chris Büttner

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Christian Buettner-167247 (11/9/2012)


    Good question, but wrong explanation (and also wrong error message from SQL Server):

    The coalesce expects at least one typed NULL in order to execute and hence the error.

    Simple example that does NOT contain a typed NULL:

    COALESCE(1,1)

    Msg 4127, Level 16, State 1, Line 8

    At least one of the arguments to COALESCE must be an expression that is not the NULL constant.

    .. possibly just missing the word "Not"

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • palotaiarpad

    SSCarpal Tunnel

    Points: 4889

    Something new to learn. But i think it comes very rare to a situation where a coelesce has only untyped null parameters.

  • This was removed by the editor as SPAM

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    Raghavendra Mudugal (11/9/2012)


    Msg 4127, Level 16, State 1, Line 8

    At least one of the arguments to COALESCE must be an expression that is not the NULL constant.

    .. possibly just missing the word "Not"

    Interesting, this is what I get:

    SELECT COALESCE(NULL,NULL)

    Msg 8133, Level 16, State 1, Line 1

    None of the result expressions in a CASE specification can be NULL.

    Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)

    May 3 2005 23:18:38

    Copyright (c) 1988-2003 Microsoft Corporation

    Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    Best Regards,

    Chris Büttner

  • Raghavendra Mudugal

    SSChampion

    Points: 10658

    Christian Buettner-167247 (11/9/2012)


    Raghavendra Mudugal (11/9/2012)


    Msg 4127, Level 16, State 1, Line 8

    At least one of the arguments to COALESCE must be an expression that is not the NULL constant.

    .. possibly just missing the word "Not"

    Interesting, this is what I get:

    SELECT COALESCE(NULL,NULL)

    Msg 8133, Level 16, State 1, Line 1

    None of the result expressions in a CASE specification can be NULL.

    Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)

    May 3 2005 23:18:38

    Copyright (c) 1988-2003 Microsoft Corporation

    Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    thats from 2012, In, SQL 2012, the message has been changed.( this actually makes more sense..)

    Microsoft SQL Server 2012 - 11.0.2218.0 (X64)

    Jun 12 2012 13:05:25

    Copyright (c) Microsoft Corporation

    Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    In SQL 2000...

    Server: Msg 8133, Level 16, State 1, Line 1

    None of the result expressions in a CASE specification can be NULL.

    Microsoft SQL Server 2000 - 8.00.194 (Intel X86) Aug 6 2000 00:57:48 Copyright (c) 1988-2000 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

    SQL 2005 SP4 and SQL 2008 R2 SP1

    Msg 4127, Level 16, State 1, Line 5

    At least one of the arguments to COALESCE must be a typed NULL.

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • Mike Dougherty-384281

    SSCrazy

    Points: 2764

    While it is an interesting exploitation of an edge case, when does this situation happen in real code?

    Does this example illustrate a Fail in the implementation/execution of coalesce()? I would expect the function to return null if all of the arguments supplied are null.

    I feel like the concept of a "typed null" is wrong. I'd say type is a property (metadata) of a variable rather than the value itself. Every example I think of to explain why leads to some sort of koan: "What is the shape of a hole?" or "What do you know about the unknown?" - It's the same kind of non-intuitive as discussions around the cardinality of infinities.

    So yeah, 'null' is a strange beast. I guess it's no surprise that sometimes it'll bite yer ***.

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

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