COALESCE - 2

Question

Post reply

COALESCE - 2

sreenivas.kumarreddy

SSC Enthusiast

Points: 143
More actions
November 8, 2012 at 8:47 pm

#269986

Comments posted to this topic are about the item COALESCE - 2
November 9, 2012 at 3:21 am

This was removed by the editor as SPAM

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

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

Lokesh Vij SSChampion Points: 10836 More actions · Answer 1

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 More actions · Answer 2

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-2 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-2 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 More actions · Answer 3

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: 259105 More actions · Answer 4

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 More actions · Answer 5

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 More actions · Answer 6

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

Stuart Davies SSCoach Points: 19505 More actions · Answer 7

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

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There 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 More actions · Answer 8

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 More actions · Answer 9

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 SSCertifiable Points: 5617 More actions · Answer 10

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

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 11

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 More actions · Answer 12

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.

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: 2794 More actions · Answer 13

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