COALESCE

  • sreenivas.kumarreddy

    SSC Enthusiast

    Points: 143

    Comments posted to this topic are about the item COALESCE

  • Ron McCullough

    SSC Guru

    Points: 63877

    Nice question - glad I read it 3 times .... subtle but nice

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • demonfox

    SSCertifiable

    Points: 6289

    gosh.. I almost overlooked the quoted null ..:w00t:

    nice question....:Whistling:

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

  • Lokesh Vij

    SSChampion

    Points: 10836

    Nice question. Luckily have not fallen into "Quoted Null" trap 🙂

    ~ 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

  • kalyani.k478

    Default port

    Points: 1400

    Nice question! learnt something new Today:-) Thank You!

  • kapil_kk

    SSC-Insane

    Points: 21316

    good start of the day with +1..

    first did R&D about coalesce thn attempt the question and select correct answer 🙂

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

  • Koen Verbeeck

    SSC Guru

    Points: 258942

    Great question. Had to read it a few times to figure out in what order the functions were called.

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

  • philip.cullingworth

    SSCrazy

    Points: 2150

    To me this feels more like a trick question than a question about coalesce. The trick was to notice that 'NULL' was quoted and not the NULL value. A valid point, but nothing to do with coalesce.

    My other gripe is that it doesn't actually use coalesce in a sensible manner. One of the things about coalesce is that you don't have to nest them as it will work through until it finds a non-NULL value. If the question was really about coalesce and not isnull then I think the query could (and should?) have been written as

    Declare @a varchar(100)

    select @a = null

    select @a = coalesce('NULL','NA',@a,'Pass')+'Fail'

    select @a

    Philip

  • Lokesh Vij

    SSChampion

    Points: 10836

    philip.cullingworth (10/17/2012)


    To me this feels more like a trick question than a question about coalesce. The trick was to notice that 'NULL' was quoted and not the NULL value. A valid point, but nothing to do with coalesce.

    My other gripe is that it doesn't actually use coalesce in a sensible manner. One of the things about coalesce is that you don't have to nest them as it will work through until it finds a non-NULL value. If the question was really about coalesce and not isnull then I think the query could (and should?) have been written as

    Declare @a varchar(100)

    select @a = null

    select @a = coalesce('NULL','NA',@a,'Pass')+'Fail'

    select @a

    Philip

    Very true as COALESCE function takes the first "Not NULL" value from the list of options. In case, we want to play around with the sequence, nesting of the funtion become very handy.

    Furthermore, Here is the correct sequence for with the COALESCE boils down to 🙂

    select @a = coalesce(@a,'NULL','NA','Pass')+'Fail'

    ~ 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

  • kapil_kk

    SSC-Insane

    Points: 21316

    But the query that you pasted is just simply give the first non null value as the coalesce does and give output as 'NULLFail'.... 😛 (no tricks in it)

    today's QOD is little tricky to make confusion between null and 'NULL'

    I am not sure about your statement regarding nesting of coalesce but I think we can do that...

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

  • philip.cullingworth

    SSCrazy

    Points: 2150

    Lokesh,

    Thanks for correcting my mistake. What comes of trying to write a response before the coffee has kicked in.

    Philip

  • Stuart Davies

    SSCoach

    Points: 18874

    Koen Verbeeck (10/17/2012)


    Great question. Had to read it a few times to figure out in what order the functions were called.

    +1

    Thanks

    -------------------------------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

  • Hugo Kornelis

    SSC Guru

    Points: 64645

    I was almost hesitant to answer the question, thinking there might be a trick thaht I was overlooking. Fortunately, I wasn't.

    The explanation is wrong, though. The innermost COALESCE does NOT return "a typed NULL", but a non-NULL string value (that just happens to be equal to the string 'NULL'). And since this is a regular, non-NULL value, all other COALESCE and ISNULL calls simply let it bubble up.

    Just for the record: the term "a typed NULL" does not mean (what the author of the question apparently thinks) "the word NULL, just typed in by me on my keyword". It means "a NULL value with data type association". This is contrast with an untyped NULL, which, obviously, is a NULL value with no data type association.

    The constant (without quotes) NULL is technically an untyped NULL. At some point, SQL Server will convert this to a typed NULL, choosing the data type based on context. If I execute "SELECT NULL + 1, NULL + 'Text', DATEADD(day, 12, NULL);", the three untyped NULLS will be converted to respectively integer, varchar(??), and one of the date/time types. If you just execute "SELECT NULL;", SQL Server will have to choose on a data type before sending it to the client, because the protocol for communication between SQL Server and the client doesn't support untyped data. I think the choice will be integer, but I'm not sure.

    A typed NULL can be achieved by using a CAST or CONVERT expression. For instance CAST(NULL as varchar(20)) is a NULL, typed as varchar(20). One way to see this in effect is to set SSMS to use "output as text", and then execute for instance "SELECT CAST(NULL as int), CAST(NULL as varchar(4)), CAST(NULL AS numeric(15,2)), CAST(NULL AS float), CAST(NULL AS datetime2);". Each of the output columns has a different length, based on the data type of the column.

    Most of the time, there is no practical difference between using typed or untyped nulls. But someimes, you do need to use a NULL value, and the default choice SQL Server makes when choosing its data type is wrong - in that case, using an explicitly typed NULL is the only option.

    PS: Sorry for going off on a tangent... None of the above is even remotely related to the question (though it is somewhat related to the mistake in the explanation).


    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • Divine Flame

    SSCoach

    Points: 15941

    Nice question. Don't see a trick in it as long as NULL is within quotes. However, explanation is absolutely incorrect:cool:


    Sujeet Singh

  • TomThomson

    SSC Guru

    Points: 104772

    demonfox (10/16/2012)


    gosh.. I almost overlooked the quoted null ..:w00t:

    nice question....:Whistling:

    I did overlook the quotes on the quoted 'NULL', so I got it wrong.

    Nice question.

    Probably the worst explanation I've ever seen - two gross mistakes, which unfortunately cancel each other out so that people might be misled into believing them. It starts with the bizarre idea that coalesce called with two non-null arguments returns null, and ends with the idea that a typed null ehen concatenated with a string magically turns from null to 'NULL'! That definitely needs fixing.

    Tom

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

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