InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 04:23:56 GMT20http://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxfor understanding this question i had taken 10 min.but got it wrong.I select 3 options.omitted seconded option.did not observed carefully.question contructed poorly.but still i think its good question.Thu, 18 Oct 2012 10:02:40 GMTmalleswarareddy_mhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxFor understanding this question I had taken 10 minutes. I have given wrong answer. I selected 3 options out of four. I omitted second option (2, NULL, 0). I didn’t observe carefully. The Question is poorly constructed. But still I think its good question.Thu, 18 Oct 2012 07:28:11 GMTmalleswarareddy_mhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspx??? very stupid question.Fri, 21 Sep 2012 07:30:13 GMTsuperishahttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxGot it correct. :-)Wed, 08 Aug 2012 12:14:16 GMTNeha05http://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxRose Bud ThanksSat, 28 Jul 2012 05:37:05 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxI read through the question a couple times, ignored the inconsistencies, learned about NULLIF(), and earned a point.Ron, you can count me as one of your satisfied customers. Thanks for your many contributions to QOTD.Fri, 27 Jul 2012 15:10:33 GMTRose Budhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspx[quote][b]L' Eomot Inversé (7/17/2012)[/b][hr]Nice straightforward question, clearly worded, no tricks. It's a pity so many who can't be bothered to read the question think that that's a fault in the question instead of in their attitude to the question.[/quote]Well spoken and thanks....Tue, 17 Jul 2012 19:06:33 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxNice straightforward question, clearly worded, no tricks. It's a pity so many who can't be bothered to read the question think that that's a fault in the question instead of in their attitude to the question.Tue, 17 Jul 2012 16:10:31 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxI agree with the question being a little confusing when a different select statement is presented, but after re-reading the text twice and ignoring the select statement I got it right.We use NULLIF because some of our code will set the value to a value outside the normal range when the column needs to be nulled.So for a text field if the param is a specific GUID/UNIQUEIDENTIFIER valuethe stored proc updates the field with NULL. For ints it is a predefined value. The caveat is to make sure the value isn't null before you perform the NULLIF:[code="sql"]SELECT NULLIF(Null, 2147483647)--Generates error--Msg 8133, Level 16, State 1, Line 1--None of the result expressions in a CASE specification can be NULL.[/code]The best bet is to wrap that puppy in a function and check to see if the passed in value has a null value and decide what to do from there or implement ISNULL in addition to NULLIF on your stored procs[code="sql"]UPDATE ...SET CountyID = NULLIF(ISNULL(@userValue, CountyID), 2147483647)...--OR UPDATE ...SET --Function with SameLogic CountyID = dbo.udf_IsDBNullInt32(@userValue, CountyID) ...[/code]It's a little more work, but it works for us and is helpful on those rare instances where we need to set the field value to null.Mon, 09 Jul 2012 13:46:15 GMTcharles.byrnehttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxBad question is not dependable. Just fix the select query to match the task. That's the big hold up?!Thu, 05 Jul 2012 06:11:06 GMTdbprogerhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspx[quote][b]WayneS (7/1/2012)[/b][hr]All others: Ron (aka bitbucket) has contributed ~ 75 QotD questions, and this is the first one that I've seen from him with this level of confusion. Give him a break... better yet, do 75 QotD questions yourself.[/quote]No. A poor question is a poor question regardless of the history.Tue, 03 Jul 2012 21:59:43 GMTPaul Whitehttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspx[quote][b]WayneS (7/1/2012)[/b][hr]Ron - thanks for the question.All others: Ron (aka bitbucket) has contributed ~ 75 QotD questions, and this is the first one that I've seen from him with this level of confusion. Give him a break... better yet, do 75 QotD questions yourself.[/quote]Wayne,That's what made this one so confusing. Usually Ron's questions are very clear, and you don't expect the "tricky" wording from him. I saw the caveat in the question, but when reviewing the answers I looked at the query, assuming the query returned only the columns listed in the answers. Having the query return an extra column, in my opinion, didn't add anything to the question, since it was about NULLIF, not about reading comprehension or attention to detail.The question and answers weren't wrong, but it could have been presented better.Mon, 02 Jul 2012 07:18:19 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxRon - thanks for the question.All others: Ron (aka bitbucket) has contributed ~ 75 QotD questions, and this is the first one that I've seen from him with this level of confusion. Give him a break... better yet, do 75 QotD questions yourself.Sun, 01 Jul 2012 20:46:27 GMTWayneShttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspx[quote][b]Toreador (6/28/2012)[/b][hr][quote][b]bitbucket-25253 (6/28/2012)[/b][hr][quote][b]My hope is, those who complained just as you have, (or made an assumption) do not read job or project specifications at work in so careless a manner and require your employer to expend unnecessary funds to complete a project according to specification.[/quote]if my job or project specifications were written as clearly as this question, then my employer would be having words with the person who drew them up ;)FWIW I noticed the qualification, but only after reading the question several times.[/quote]It was a poorly worded question :satisfied:Thu, 28 Jun 2012 14:19:28 GMTmsmithsonhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspx[quote][b]bitbucket-25253 (6/28/2012)[/b][hr][quote][b]My hope is, those who complained just as you have, (or made an assumption) do not read job or project specifications at work in so careless a manner and require your employer to expend unnecessary funds to complete a project according to specification.[/quote]if my job or project specifications were written as clearly as this question, then my employer would be having words with the person who drew them up ;)FWIW I noticed the qualification, but only after reading the question several times.Thu, 28 Jun 2012 09:25:40 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspx[quote][b]Danny Ocean (6/28/2012)[/b][hr]Hi,Very badly represent the question. given option are not correct. your are showing only 3 values (columns) in option but after execute the query, you will find the 4 columns.yyoVinay[/quote][b]If you read the question properly you should have seen a clear and distinct sample of an answer. in the first four lines of text as shown below.Highlighted in bold text to assist you in reading what you should have read before attempting to answer the question.[/b]----NULLIF 1QUESTION: What values are returned for Test 1 and Test 2? (select all that apply) [b]The values are listed as ID, TEST1, TEST2. For example: 1, 3, 0[/b] ---My hope is, those who complained just as you have, (or made an assumption) do not read job or project specifications at work in so careless a manner and require your employer to expend unnecessary funds to complete a project according to specification.Thu, 28 Jun 2012 09:04:59 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxHi,Very badly represent the question. given option are not correct. your are showing only 3 values (columns) in option but after execute the query, you will find the 4 columns.VinayThu, 28 Jun 2012 07:05:43 GMTDanny Oceanhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxMe too got confused assuming second column is value of Y unprocessed! :(Neverthless, learned about NULLIF(). Thanks!Tue, 26 Jun 2012 22:33:40 GMTShripad Kandharkarhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxI know about NULLIF, so I should have gotten it right. But the presence of the (unused) column X in the table confused me; I kept checking answers as if the query used X in the first NULLIF and Y in the second. I corrected one of the two mistakes I made because of that before submitting, then saw the second the moment I clicked submit. Ah well.I wanted to explain how NULLIF can very easily prevent division by zero errors, but I see someone already did that.Tue, 26 Jun 2012 15:05:58 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspx[quote][b]SQLRNNR (6/26/2012)[/b][hr]Straightforward question[/quote][b]Thanks[/b] ... but read the previous entries and see the opposite feelings. I was beaten from pillow to post ...Tue, 26 Jun 2012 12:29:15 GMTbitbucket-25253http://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxNice easy and straightforward.Fully reading the question, and waiting until after lunch probably helped me. Never seem to get even the easy ones right in the morning.Tue, 26 Jun 2012 12:28:23 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxStraightforward questionTue, 26 Jun 2012 10:15:32 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxThanks for the question Ron.Tue, 26 Jun 2012 09:52:01 GMTOzYbOi d(-_-)bhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxFavorite use for NullIf is to prevent a divide by zero:Select Miles / Nullif( Hours, 0 ) as SpeedFrom SomeTableWasn'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.Tue, 26 Jun 2012 09:17:03 GMTbrdudleyhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxGood question, though I agree it is not properly worded both in the question and in the answer."El" Jerry.Tue, 26 Jun 2012 09:15:27 GMTEL Jerryhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxGreat QotD, Ron. Thanks!Tue, 26 Jun 2012 09:01:01 GMTRevenanthttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspx[quote][b]Thomas Abraham (6/26/2012)[/b][hr]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.[/quote]Ditto (and thank you, Carlo, for explaining when NULLIF() might be useful).[i]Moral: Bless Professor Abraham, and you will be blessed.[/i] :-)Tue, 26 Jun 2012 08:43:27 GMTMichael Poppershttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxI 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,DavidTue, 26 Jun 2012 08:14:03 GMTdawargohttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxThanks for the question. I never used nullif before however I do recall seeing it in some Oracle sql before.Tue, 26 Jun 2012 08:05:48 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxVery interesting question.Thanks for sharing.:-)Tue, 26 Jun 2012 07:48:58 GMTHaininghttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxI 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.Tue, 26 Jun 2012 07:40:19 GMTraulggonzalezhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspx[quote][b]paul.knibbs (6/26/2012)[/b][hr]I'd agree with Michael--this question seemed to be unnecessarily obfuscated and was more about reading comprehension than anything SQL related, IMHO.[/quote]Agreed. The question is poorly constructed, with part of the requirements outside of the image, and part of them in the image.Tue, 26 Jun 2012 07:09:00 GMTseth delcontehttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxThe 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.Tue, 26 Jun 2012 06:53:20 GMTcathy.bakerhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxBy 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:[code="sql"]SELECT cArticle,mPrice FROM tbPricesWHERE GETDATE() BETWEEN dtStart AND ISNULL(NULLIF(dtEnd,'1799-12-31'),GETDATE())[/code]Tue, 26 Jun 2012 06:46:47 GMTCarlo Romagnanohttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxI agree with Imrann. Today's question is badly framed and confusing....but, thanks to the question, I learnt the use of NullIfTue, 26 Jun 2012 06:45:30 GMTradhameehttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxHaving 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.Tue, 26 Jun 2012 06:23:32 GMT Jack Corbetthttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxI 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.Tue, 26 Jun 2012 06:18:19 GMTMike Is Herehttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxThanks 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.Tue, 26 Jun 2012 05:45:42 GMTThomas Abrahamhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspx[quote][b]Koen Verbeeck (6/25/2012)[/b][hr]Haven't used NULLIF before, so I learned something.Small error: the select statement selects 4 columns, the answers have only 3.[/quote];-)Tue, 26 Jun 2012 05:16:09 GMTJoseACJrhttp://www.sqlservercentral.com/Forums/Topic1320989-1222-1.aspxI'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:[code="sql"]select id, nullif(y,0) as 'test 1', nullif(y,9) as 'test 2'from #whatifwhere nullif(y,0) is nullor nullif(y,9) is null[/code]and then have the same 4 answers with a "Check all that apply" option?Tue, 26 Jun 2012 04:33:44 GMTchriscoates