NULLIF 1

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by bitbucket » NULLIF 1

53 posts, Page 2 of 6

««1 2 3 4 5 »»»

NULLIF 1

Rate Topic

Display Mode

Topic Options

Author

Message

danielfountain

Posted Tuesday, June 26, 2012 1:47 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 4:03 AM
Points: 505, Visits: 589

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

Post #1321067

Michael Riemer

Posted Tuesday, June 26, 2012 2:08 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 2:18 AM
Points: 1,261, Visits: 356

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?

Post #1321083

paul.knibbs

Posted Tuesday, June 26, 2012 2:36 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 8:24 AM
Points: 1,272, Visits: 4,309

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

Post #1321093

chriscoates

Posted Tuesday, June 26, 2012 4:33 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, November 09, 2012 7:25 AM
Points: 298, Visits: 107

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?

Post #1321128

JoseACJr

Posted Tuesday, June 26, 2012 5:16 AM

SSC-Addicted

Group: General Forum Members
Last Login: Tuesday, June 04, 2013 11:06 AM
Points: 447, Visits: 234

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.

Post #1321148

Thomas Abraham

Posted Tuesday, June 26, 2012 5:45 AM

Ten Centuries

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 5:11 AM
Points: 1,168, Visits: 1,470

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.

Please don't go. The drones need you. They look up to you.

Post #1321163

Mike Is Here

Posted Tuesday, June 26, 2012 6:18 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 7:23 AM
Points: 1,355, Visits: 436

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.

Post #1321177

Jack Corbett

Posted Tuesday, June 26, 2012 6:23 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 10:01 AM
Points: 10,613, Visits: 11,956

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

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

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
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2

Post #1321180

radhamee

Posted Tuesday, June 26, 2012 6:45 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, November 12, 2012 9:39 AM
Points: 26, Visits: 46

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

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

Post #1321192

Carlo Romagnano

Posted Tuesday, June 26, 2012 6:46 AM

SSCommitted

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 1,996, Visits: 1,864

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

Post #1321194

« Prev Topic | Next Topic »

53 posts, Page 2 of 6

««1 2 3 4 5 »»»

Permissions