June 26, 2012 at 9:52 am
Thanks for the question Ron.
June 26, 2012 at 10:15 am
Straightforward question
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 26, 2012 at 12:28 pm
Nice 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.
June 26, 2012 at 12:29 pm
SQLRNNR (6/26/2012)
Straightforward question
Thanks ... but read the previous entries and see the opposite feelings. I was beaten from pillow to post ...
June 26, 2012 at 3:05 pm
I 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.
June 26, 2012 at 10:33 pm
Me too got confused assuming second column is value of Y unprocessed! 🙁
Neverthless, learned about NULLIF(). Thanks!
June 28, 2012 at 7:05 am
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.
Vinay
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!
June 28, 2012 at 9:04 am
Danny Ocean (6/28/2012)
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.
yyo
Vinay
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.
----
NULLIF 1
QUESTION: What values are returned for Test 1 and Test 2? (select all that apply)
The values are listed as ID, TEST1, TEST2. For example:
1, 3, 0
---
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.
June 28, 2012 at 9:25 am
bitbucket-25253 (6/28/2012)
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.
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.
June 28, 2012 at 2:19 pm
Toreador (6/28/2012)
bitbucket-25253 (6/28/2012)
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.
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.
It was a poorly worded question :satisfied:
July 1, 2012 at 8:46 pm
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.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 2, 2012 at 7:18 am
WayneS (7/1/2012)
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.
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.
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
July 3, 2012 at 9:59 pm
WayneS (7/1/2012)
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.
No. A poor question is a poor question regardless of the history.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
July 5, 2012 at 6:11 am
Bad question is not dependable. Just fix the select query to match the task. That's the big hold up?!
July 9, 2012 at 1:46 pm
I 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 value
the 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:
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.
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
UPDATE ...
SET
CountyID = NULLIF(ISNULL(@userValue, CountyID), 2147483647)
...
--OR
UPDATE ...
SET
--Function with SameLogic
CountyID = dbo.udf_IsDBNullInt32(@userValue, CountyID)
...
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.
"There is nothing so useless as doing efficiently that which should not be done at all." - Peter Drucker
Viewing 15 posts - 31 through 45 (of 52 total)
You must be logged in to reply to this topic. Login to reply