Post reply

Space missing in SELECT statement

  • October 21, 2013 at 5:28 am

    #1660093

    Good trick question. Monday morning, brain wasn't working yet.

    Thanks,

    ToddR

  • October 21, 2013 at 5:59 am

    #1660098

    +1

    ---------------
    Mel. 😎

  • October 21, 2013 at 6:45 am

    #1660118

    Wow! Didn't see that coming. Good Question.

  • October 21, 2013 at 6:59 am

    #1660123

    I admit it, I got caught by the "fromTest" 😀

    This is yet another example of how badly designed SQL syntax really is, from an ambiguity standpoint. If the "AS" keyword was required this trap wouldn't exist. (laughing)

  • October 21, 2013 at 8:41 am

    #1660159

    paul.knibbs (10/21/2013)

    Seems I get caught out by this alias behaviour every time they ask a question about it... :pinch:

    +1

    Great question!

    Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]

  • October 21, 2013 at 9:36 am

    #1660177

    Nice question. I think it's a more interesting question if you try this select:

    SELECT 1

    FROM TEST

    or even this select:

    SELECT [1]

    FROM TEST

    [font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
    Connect to me on LinkedIn

  • October 21, 2013 at 9:53 am

    #1660183

    You got me on that one! These questions are kinda fun once in a while. Maybe it should have been saved for April 1.

    Tony
    ------------------------------------
    Are you suggesting coconuts migrate?

  • October 21, 2013 at 10:18 am

    #1660193

    Hah hah hah! Neat! Thanks, Ashish!

  • October 21, 2013 at 11:28 am

    #1660207

    crussell-931424 (10/21/2013)

    I can't win on these. If you read it exactly as it is written then people complain that we should have understood what the question was really asking in spite of the "accidental" typo. So I figure this must just be another "accidental" type and I correct it, separating the "from" from the the "TEST." But no, this time the question is attempting to trick us. If it wasn't then why use fromTEST as the alias? No one would ever use that intentionally. So I guess I don't know what the lesson is here. If it is to identify errors in code, then I passed. I found the error and corrected it. If it is to pretend that someone would actually name an alias "fromTEST" and want to know the results, then I would say, don't waste my time.

    Here the lesson is about handling of alias in sql server.

  • October 21, 2013 at 11:30 am

    #1660208

    Revenant (10/21/2013)

    Hah hah hah! Neat! Thanks, Ashish!

    Welcome.

  • October 21, 2013 at 1:01 pm

    #1660244

    Fun question. Thanks Ashish.

  • October 21, 2013 at 1:29 pm

    #1660262

    crussell-931424 (10/21/2013)

    I can't win on these. If you read it exactly as it is written then people complain that we should have understood what the question was really asking in spite of the "accidental" typo. So I figure this must just be another "accidental" type and I correct it, separating the "from" from the the "TEST." But no, this time the question is attempting to trick us. If it wasn't then why use fromTEST as the alias? No one would ever use that intentionally. So I guess I don't know what the lesson is here. If it is to identify errors in code, then I passed. I found the error and corrected it. If it is to pretend that someone would actually name an alias "fromTEST" and want to know the results, then I would say, don't waste my time.

    When I saw the question in the newsletter, I assumed the missing space was a typo. But after seeing that one of the answer options actually mentioned the missing space, I knew that this must have been deliberate. I fail to see how you can think the question author could have accidentally missed a space he intended to be there.

    As to what can be learned from this question - have you never stared for half an hour or more at an inexplicable error message being returned from what appears to be perfectly valid SQL before finally noticing the missing comma in

    INSERT INTO dbo.TargetTable (ColA, ColB, ColC, ColD, ColE)

    SELECT (Complicated + Expression) / That

    * Nevertheless * (1 - IsSyntacticallyValid) -- ColA

    ColB, -- ColB

    'String constant', -- ColC

    17.0 * (1 - ColD), -- ColD

    NULL -- ColE

    FROM dbo.SourceTable;

    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/

  • October 21, 2013 at 10:53 pm

    #1660367

    Hugo Kornelis (10/21/2013)

    crussell-931424 (10/21/2013)

    I can't win on these. If you read it exactly as it is written then people complain that we should have understood what the question was really asking in spite of the "accidental" typo. So I figure this must just be another "accidental" type and I correct it, separating the "from" from the the "TEST." But no, this time the question is attempting to trick us. If it wasn't then why use fromTEST as the alias? No one would ever use that intentionally. So I guess I don't know what the lesson is here. If it is to identify errors in code, then I passed. I found the error and corrected it. If it is to pretend that someone would actually name an alias "fromTEST" and want to know the results, then I would say, don't waste my time.

    When I saw the question in the newsletter, I assumed the missing space was a typo. But after seeing that one of the answer options actually mentioned the missing space, I knew that this must have been deliberate. I fail to see how you can think the question author could have accidentally missed a space he intended to be there.

    As to what can be learned from this question - have you never stared for half an hour or more at an inexplicable error message being returned from what appears to be perfectly valid SQL before finally noticing the missing comma in

    INSERT INTO dbo.TargetTable (ColA, ColB, ColC, ColD, ColE)

    SELECT (Complicated + Expression) / That

    * Nevertheless * (1 - IsSyntacticallyValid) -- ColA

    ColB, -- ColB

    'String constant', -- ColC

    17.0 * (1 - ColD), -- ColD

    NULL -- ColE

    FROM dbo.SourceTable;

    One more Nice example

  • October 22, 2013 at 8:45 am

    #1660567

    For once I didn't fall for the trick!

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • October 22, 2013 at 12:15 pm

    #1660637

    T.Ashish (10/21/2013)

    crussell-931424 (10/21/2013)

    I can't win on these. If you read it exactly as it is written then people complain that we should have understood what the question was really asking in spite of the "accidental" typo. So I figure this must just be another "accidental" type and I correct it, separating the "from" from the the "TEST." But no, this time the question is attempting to trick us. If it wasn't then why use fromTEST as the alias? No one would ever use that intentionally. So I guess I don't know what the lesson is here. If it is to identify errors in code, then I passed. I found the error and corrected it. If it is to pretend that someone would actually name an alias "fromTEST" and want to know the results, then I would say, don't waste my time.

    Here the lesson is about handling of alias in sql server.

    +1 Ashish.

    Crussell, I would have said exactly the same but the subject of the question clears the doubt... 🙂

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

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