NULLIF 1

Question

NULLIF 1

Viewing 15 posts - 16 through 30 (of 52 total)

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

Thomas Abraham SSChampion Points: 10761 More actions · Answer 1

Thanks for the question. Never used NULLIF, so forced me to investigate.

I got my point, but am inclined to agree with those that feel that the question could have been presented in a bit more straightforward manner, and still have achieved the same end.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

Mike Is Here Hall of Fame Points: 3348 More actions · Answer 2

I have to agree with every one else. The select statement is confusing.

First you start off with the declaration

"What values are returned for Test 1 and Test 2? " which is invalid because there are 3 columns.

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

Then a four column select. What was the point of making a four column select, just make it line up with the answer.

Jack Corbett SSC Guru Points: 184393 More actions · Answer 3

Having a different number of columns in the query vs. the answers is wrong. In no case would the query supplied have a 2nd column return NULL. I know how NULLIF works and have used it. IN my opinion this is one of the worst QotD's I've seen.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

radhamee Valued Member Points: 52 More actions · Answer 4

I agree with Imrann. Today's question is badly framed and confusing....

but, thanks to the question, I learnt the use of NullIf

Carlo Romagnano SSC-Insane Points: 22603 More actions · Answer 5

By the way:

I often use NULLIF when 3rd party application use MAGIC VALUES instead of NULL.

e.g. 1799-12-31 stands for NULL DATETIME.

Here's a generic example to get an actual price:

SELECT cArticle,mPrice FROM tbPrices

WHERE GETDATE() BETWEEN dtStart AND ISNULL(NULLIF(dtEnd,'1799-12-31'),GETDATE())

cathy.baker Hall of Fame Points: 3412 More actions · Answer 6

The QOD should be vetted and tested before posting for all to answer. None of the answers are correct because the select statement returns 4 columns, not 3. So if you got the point for the question, that's the same as everyone gets a trophy. It counts in your collection, but it has no value.

seth delconte SSCertifiable Points: 6388 More actions · Answer 7

paul.knibbs (6/26/2012)
I'd agree with Michael--this question seemed to be unnecessarily obfuscated and was more about reading comprehension than anything SQL related, IMHO.

Agreed. The question is poorly constructed, with part of the requirements outside of the image, and part of them in the image.

_________________________________
seth delconte
http://sqlkeys.com

SQLDoubleG Hall of Fame Points: 3100 More actions · Answer 8

I completely agree with many other peers.

My first answer was that none of them could be returned by that query (4 cols in select statement, only 3 values per row.. WTF)

So because I had to choose at least one (mandatory) then I read the explanation...

Moral for this QotD ? Read everything before answering.

PD. Better questions will bring better answers.

Haining SSCrazy Points: 2118 More actions · Answer 9

Very interesting question.

Thanks for sharing.:-)

Ken SSCoach Points: 17191 More actions · Answer 10

Thanks for the question. I never used nullif before however I do recall seeing it in some Oracle sql before.

dawargo SSC-Addicted Points: 465 More actions · Answer 11

I have to concur with the majority. When you actually run that query and get the four columns the second and fourth rows are completely wrong in SQL 2008 which is where the question cites as a reference.

But technically none of the answers are correct, since none of the column values are suppressed as intimated in the answer.

Just my .02.

Peace,

David

Michael Poppers SSCrazy Points: 2150 More actions · Answer 12

Thomas Abraham (6/26/2012)
Thanks for the question. Never used NULLIF, so forced me to investigate.
I got my point, but am inclined to agree with those that feel that the question could have been presented in a bit more straightforward manner, and still have achieved the same end.

Ditto (and thank you, Carlo, for explaining when NULLIF() might be useful).

Moral: Bless Professor Abraham, and you will be blessed. 🙂

Revenant SSC-Forever Points: 42467 More actions · Answer 13

Revenant

SSC-Forever

Points: 42467

June 26, 2012 at 9:01 am

#1505629

Great QotD, Ron. Thanks!

EL Jerry SSCertifiable Points: 7061 More actions · Answer 14

Good question, though I agree it is not properly worded both in the question and in the answer.

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

brdudley SSCrazy Points: 2320 More actions · Answer 15

Favorite use for NullIf is to prevent a divide by zero:

Select Miles / Nullif( Hours, 0 ) as Speed

From SomeTable

Wasn't thrilled with the formulation of the questions, but I don't think it was designed to confuse. Likely, the image was inadvertently taken with the extra column and the note at the top was designed to rectify the problem.

Not a big fan of the "image instead of text" questions...

Seems like plenty of people learned about a function they weren't aware of, so the question served a purpose despite its flaws.