Post reply

NULLIF 1

  • June 26, 2012 at 9:52 am

    #1505672

    Thanks for the question Ron.

  • June 26, 2012 at 10:15 am

    #1505689

    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

    #1505774

    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.


    --Mark Tassin
    MCITP - SQL Server DBA
    Proud member of the Anti-RBAR alliance.
    For help with Performance click this link[/url]
    For tips on how to post your problems[/url]

  • June 26, 2012 at 12:29 pm

    #1505775

    SQLRNNR (6/26/2012)

    Straightforward question

    Thanks ... but read the previous entries and see the opposite feelings. I was beaten from pillow to post ...

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • June 26, 2012 at 3:05 pm

    #1505863

    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.

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

  • June 26, 2012 at 10:33 pm

    #1505961

    Me too got confused assuming second column is value of Y unprocessed! 🙁

    Neverthless, learned about NULLIF(). Thanks!

  • June 28, 2012 at 7:05 am

    #1506589

    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

    #1506675

    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.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • June 28, 2012 at 9:25 am

    #1506689

    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

    #1506839

    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

    #1507588

    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

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • July 2, 2012 at 7:18 am

    #1507725

    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

    #1508566

    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

    #1509060

    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

    #1510553

    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