That case filter

  • My first assumption was that the final union all was intentional and the test was that the union was being inserted into the same table that was being selected from. If I had stopped to think a little more I would have realized that had that been the test and worked then the correct answer wouldn't have been an option. I'm still not sure what I think about doing something nitpicky in light of some of the recent mistakes but I do appreciate what you were trying to do.

    I can't think of any SQL stories where I've done something on a similar level even though I know they do exist. What does come to mind is hitting the delete key to delete something in a web app and having it not work. Then complaining to a web developer on my team about how I couldn't edit what was in the box and having him point out (politely) that I was at the end of the field and delete remove the character after the cursor, not before.

  • cfradenburg (7/20/2011)


    I can't think of any SQL stories where I've done something on a similar level even though I know they do exist. What does come to mind is hitting the delete key to delete something in a web app and having it not work. Then complaining to a web developer on my team about how I couldn't edit what was in the box and having him point out (politely) that I was at the end of the field and delete remove the character after the cursor, not before.

    Ah that's a real nice one :w00t:.

    I had the same issue with SSC. I thought I was going crazy untill Steve told me that he had deleted the delete button.

  • Ambiguous, and nothing to to with case whatsover. Is it me or is there a growing trend for such 'dodgy' questions posted with the sole intent of annoying people rather than making them think first thing in the morning? Boo!

  • Strangely when I run this code and do a select on the table I get 8 rows.

    (SQL Server 2000)

  • I liked this question. I didn't think it was too tricky.

    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

  • david.moule (7/20/2011)


    Strangely when I run this code and do a select on the table I get 8 rows.

    (SQL Server 2000)

    Can't happen unless table variables behaved diffrently in sql 2K (which I can't test). Also I'd like to see what code you ran to get those results!

  • Trevor Tinsley (7/20/2011)


    Ambiguous, and nothing to to with case whatsover. Is it me or is there a growing trend for such 'dodgy' questions posted with the sole intent of annoying people rather than making them think first thing in the morning? Boo!

    Please read my first post. I explain my reasoning behind the question.

  • Trevor Tinsley (7/20/2011)


    Ambiguous, and nothing to to with case whatsover. Is it me or is there a growing trend for such 'dodgy' questions posted with the sole intent of annoying people rather than making them think first thing in the morning? Boo!

    Actually, there have been a lot less of these. And I understand why Ninja used the name he did. Calling it, "Extra union," would have made it far too obvious. In the intro to "The Name of the Rose" by Umberto Eco the author talks about book titles and says that the title shouldn't reveal anything about the book at all. If I remember correctly he points to The Three Musketeers as a great title because the book isn't about any of the three but about someone else which quickly become obvious.

  • SQLRNNR (7/20/2011)


    I liked this question. I didn't think it was too tricky.

    Tell the truth now, did you get it right WITHOUT running the code?

    I'd love to see the real % of correct answer for those people :w00t:.

  • cfradenburg (7/20/2011)


    Trevor Tinsley (7/20/2011)


    Ambiguous, and nothing to to with case whatsover. Is it me or is there a growing trend for such 'dodgy' questions posted with the sole intent of annoying people rather than making them think first thing in the morning? Boo!

    Actually, there have been a lot less of these. And I understand why Ninja used the name he did. Calling it, "Extra union," would have made it far too obvious. In the intro to "The Name of the Rose" by Umberto Eco the author talks about book titles and says that the title shouldn't reveal anything about the book at all. If I remember correctly he points to The Three Musketeers as a great title because the book isn't about any of the three but about someone else which quickly become obvious.

    Ah I had never thought of it that way.

    Tx :hehe:

  • Ninja's_RGR'us (7/20/2011)


    SQLRNNR (7/20/2011)


    I liked this question. I didn't think it was too tricky.

    Tell the truth now, did you get it right WITHOUT running the code?

    I'd love to see the real % of correct answer for those people :w00t:.

    Nope - didn't run the code. Thought about it but didn't

    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

  • Ninja's_RGR'us (7/20/2011)


    Rob Schripsema (7/20/2011)


    Toreador (7/20/2011)


    My original thought was that there would be no rows returned as this was an insert.

    But I then thought that was silly, and I was being unfair on the questioner who had made an honest and obvious mistake - there are too many questions that provoke pedantic discussions whyen the meaning of the question is clear.

    So I answered the question as if it had been 'how many rows are inserted'.

    Given recent questions, I think this was a fair assumption to make.

    My reasoning, as well. To the author of the question, the wording may have made sense; he knew what he'd been trying to do and couldn't figure out why he wasn't getting the results he expected. To someone coming at the question "out of the blue", the question itself didn't make sense. We were left to make our own assumptions about what may have been meant and obviously many of us got it wrong -- not the answer, but the assumption about what the author meant.

    Poor question.

    It's real life, I was thrown into this situation myself out of the blue, with the wrong assumptions.

    We are profesionnals, it's what we do. It's what we need to be able to do day in and day out.

    Sorry you didn't like it. I still hope you get something out of this thread.

    🙂

    I'd still like to argue that the scenario you encountered ("why doesn't this SQL code return any rows?") would lead you to a completely differerent set of assumptions than the scenario which WE encountered ("the question doesn't make sense -- the SELECT returns rows but they're inserted into the table variable, so does he mean how many were selected/inserted, or how many were returned to the executing process?").

    But you are correct -- we need to handle bizarre situations like this if we're going to call ourselves "professionals" (who can spell :-P). I learned something here -- but not about SQL. Thanks!

    Rob Schripsema
    Propack, Inc.

  • david.moule (7/20/2011)


    Strangely when I run this code and do a select on the table I get 8 rows.

    (SQL Server 2000)

    Code and execution plan would be good to see. I tried this on a SQL 2000 box we have and got 5 rows. Parallelism may be coming into play which is what I initially thought the question turned around.

  • Rob Schripsema (7/20/2011)


    I'd still like to argue that the scenario you encountered ("why doesn't this SQL code return any rows?") would lead you to a completely differerent set of assumptions than the scenario which WE encountered ...

    Actually "that Darn Case statement" was my answer to your great question where I started wasting time.

    I had a hard time putting all those circumstances into a single 4-5 word title, and without giving it all away at the same time.

    God knows I tried! 😉

    Glad you liked it!

  • cfradenburg (7/20/2011)


    david.moule (7/20/2011)


    Strangely when I run this code and do a select on the table I get 8 rows.

    (SQL Server 2000)

    Code and execution plan would be good to see. I tried this on a SQL 2000 box we have and got 5 rows. Parallelism may be coming into play which is what I initially thought the question turned around.

    Good idea, please post the text plan.

    I think (hope) that if you run the code from ssms you can still get the .sqlplan which would be even better.

Viewing 15 posts - 121 through 135 (of 200 total)

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