Output of Query

  • Nice question - thanks

    -------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
    There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
    I would never join a club that would allow me as a member - Groucho Marx

  • sqlzealot-81 (10/10/2011)


    I guess, you can achieve by the below way,

    SELECT

    CASE IsNull(COL1,'') WHEN '' THEN 'Is Null'

    WHEN '0' THEN 'Is Zero'

    WHEN '1' THEN 'Is One'

    WHEN '2' THEN 'Is Two'

    END

    FROM

    (

    SELECT NULL AS Col1

    UNION

    SELECT '0' AS Col1

    UNION

    SELECT '1' AS Col1

    UNION

    SELECT '2' AS Col1

    ) TMP

    This works except you can't differentiate between null and empty strings because your isnull will now treat them both the same. In some cases this is ok but in others it isn't.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I got an error when I executed the code - oh well :hehe:

  • Sean Lange (10/10/2011)[hrThis works except you can't differentiate between null and empty strings because your isnull will now treat them both the same. In some cases this is ok but in others it isn't.

    If it matters then you can do something like

    CASE IsNull(COL1,'avaluethatwillnotappearinthedata') WHEN 'avaluethatwillnotappearinthedata' THEN 'Is Null'

  • Or:

    SELECT

    CASE when Col1 is null then 'Is Null'

    else case col1

    WHEN '0' THEN 'Is Zero'

    WHEN '1' THEN 'Is One'

    WHEN '2' THEN 'Is Two'

    END

    end

    FROM

    (

    SELECT NULL AS Col1

    UNION

    SELECT '0' AS Col1

    UNION

    SELECT '1' AS Col1

    UNION

    SELECT '2' AS Col1

    ) TMP

  • Thanks for the 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

  • Good question, luckily read the question twice before answering.

  • Bugger! I hate it when I know the right answer but click on the wrong choice! :angry:

  • Simple but neat -- thank you!

  • Nice question about something that comes up regularly for me. Especially in writing queries for reporting.

  • Great question - unfortunately I got it wrong.

    I thought it was too simple so I over compensated and assumed that the code would have "set ansi_nulls off" for the session since it is effectively saying "where col1 = null" rather than "where col1 is null".

  • good question - tks

  • I'd like to suggest a modification of the answer. What is happening is that the "CASE COLUMN WHEN" syntax does an equity comparision and then uses the result of that comparision, which of course fails with NULL, while the "CASE WHEN x" syntax uses the result of x. Which is why the given query returns null.

    So, something like:

    The CASE's statement has two alternative syntaxs, "CASE ColumnName WHEN Value" does an equity comparision between the column and the given value. This fails with NULLS unless ANSI_NULLS is off. The other syntax is "CASE WHEN EXPRESSION", which evalutes an expression (which may use any available columns) and uses the result of that expression to determine whether the condition has been met and the associated THEN used.

  • Thanks for the question. It was a good caffination check this morning. 🙂


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

  • Excellent question. Thanks!

    Rob Schripsema
    Propack, Inc.

Viewing 15 posts - 16 through 30 (of 39 total)

You must be logged in to reply to this topic. Login to reply