NULLIF 1

Question

Post reply

NULLIF 1

Ron McCullough

SSC Guru

Points: 63877
More actions
June 25, 2012 at 9:38 pm

#392601

Comments posted to this topic are about the item NULLIF 1
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]

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

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

Koen Verbeeck SSC Guru Points: 259085 More actions · Answer 1

Haven't used NULLIF before, so I learned something.

Small error: the select statement selects 4 columns, the answers have only 3.

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

rhythmk SSCertifiable Points: 7271 More actions · Answer 2

One more function added in my memory DB 🙂

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

rhythmk SSCertifiable Points: 7271 More actions · Answer 3

Koen Verbeeck (6/25/2012)
Haven't used NULLIF before, so I learned something.
Small error: the select statement selects 4 columns, the answers have only 3.

Nice observation Koen 🙂

--rhythmk
------------------------------------------------------------------
To post your question use below link

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂

imarran SSC-Addicted Points: 489 More actions · Answer 4

badly formed answers

you are selecting 4 columns in your question (ID, Y, Test1, Test2) yet your answers and the supposed "correct" one only has 3

palotaiarpad SSCertifiable Points: 5617 More actions · Answer 5

imarran (6/26/2012)
badly formed answers
you are selecting 4 columns in your question (ID, Y, Test1, Test2) yet your answers and the supposed "correct" one only has 3

Say, not optimal, but if you read the complete task 'The values are listed as ID, TEST1, TEST2. For example: 1, 3, 0', then it should be clear.:-P

I searched the trick behind the question, but found nothing, so all 4 selected, and won a point. Thx!

Carlo Romagnano SSC-Insane Points: 22596 More actions · Answer 6

Koen Verbeeck (6/25/2012)
Small error: the select statement selects 4 columns, the answers have only 3.

BIG ERROR: I answered thinking at the first 3 columns, so that Y would return without conversion.

iBar SSCertifiable Points: 5872 More actions · Answer 7

Yes i Agree,

However in second line of question, following is also mentioned.

"The values are listed as ID, TEST1, TEST2. For example: "

So reading full question and all details can avoid such confusions.

Cheers

derek.colley SSCrazy Eights Points: 8040 More actions · Answer 8

Caught me out there, I returned 3 of the 4 correct answers. 2,NULL,0 I thought is not valid. Well done.

---

Note to developers:
CAST(SUBSTRING(CAST(FLOOR(NULLIF(ISNULL(COALESCE(1,NULL),NULL),NULL)) AS CHAR(1)),1,1) AS INT) == 1
So why complicate your code AND MAKE MY JOB HARDER??!:crazy:

Want to get the best help? Click here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help (Jeff Moden)
My blog: http://uksqldba.blogspot.com
Visit http://www.DerekColley.co.uk to find out more about me.

imarran SSC-Addicted Points: 489 More actions · Answer 9

Is the goal of the question to catch people out or test SQL skill and knowledge?

danielfountain SSCarpal Tunnel Points: 4229 More actions · Answer 10

I was confused due to the lack of the correct number of columns.

Michael Riemer SSCertifiable Points: 5190 More actions · Answer 11

A great topic to test knowledge, but I think it kind of gets lost within the context of the question! :hehe:

What is the point of the tricks in the question (result at the start, and four columns in the select, with three in the answer) when trying to test a certain functionality, along with having the column X in the mix?

paul.knibbs SSCoach Points: 15320 More actions · Answer 12

I'd agree with Michael--this question seemed to be unnecessarily obfuscated and was more about reading comprehension than anything SQL related, IMHO.

chriscoates Mr or Mrs. 500 Points: 520 More actions · Answer 13

I've been caught out on the QotD before by not reading the question properly, but I agree that the SQL being different to the actual quesiton seemed a little overly confusing.

However, as there's 4 records in the table, regardless of knowledge of the NULLIF function surely the output was always going to be all 4 options?

Perhaps to test knowledge of NULLIF, the question might have been better to include NULLIF as a where clause, something like:

select

id,

nullif(y,0) as 'test 1',

nullif(y,9) as 'test 2'

from

#whatif

where

nullif(y,0) is null

or nullif(y,9) is null

and then have the same 4 answers with a "Check all that apply" option?

JoseACJr SSC Eights! Points: 934 More actions · Answer 14

Koen Verbeeck (6/25/2012)
Haven't used NULLIF before, so I learned something.
Small error: the select statement selects 4 columns, the answers have only 3.

😉