Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

NULLIF 1 Expand / Collapse
Author
Message
Posted Tuesday, June 26, 2012 1:47 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, August 1, 2014 5:45 AM
Points: 766, Visits: 840
I was confused due to the lack of the correct number of columns.
Post #1321067
Posted Tuesday, June 26, 2012 2:08 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 1:38 AM
Points: 1,755, Visits: 477
A great topic to test knowledge, but I think it kind of gets lost within the context of the question!

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
Posted Tuesday, June 26, 2012 2:36 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 9:16 AM
Points: 1,632, Visits: 5,585
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
Posted Tuesday, June 26, 2012 4:33 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, November 9, 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
Posted Tuesday, June 26, 2012 5:16 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Thursday, August 28, 2014 5:49 AM
Points: 531, Visits: 274
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
Posted Tuesday, June 26, 2012 5:45 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, August 20, 2014 8:26 AM
Points: 1,803, Visits: 2,168
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.
Connect to me on LinkedIn
Post #1321163
Posted Tuesday, June 26, 2012 6:18 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 3:27 PM
Points: 1,393, Visits: 479
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
Posted Tuesday, June 26, 2012 6:23 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 4:51 PM
Points: 11,264, Visits: 13,023
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
Posted Tuesday, June 26, 2012 6:45 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC 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
Posted Tuesday, June 26, 2012 6:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 12:53 AM
Points: 2,502, Visits: 2,384
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 »

Add to briefcase ««12345»»»

Permissions Expand / Collapse