Results 1 to 8 of 8

Thread: T-SQL or ISO

  1. #1
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912

    Unanswered: T-SQL or ISO

    Hi chumps...I mean chaps

    I saw something new today. Wondered if anyone else had seen it, and if it was T-SQL specific or ISO. BoL entry for SELECT does not suggest it is valid, but it is. Whaddaya reckon the result will be? (tested in SQL 2005 & 2008)
    Code:
    SELECT  name
          , (object_id)
          , (SELECT schema_id) 
          , (SELECT TOP 937 type_desc)
    FROM    sys.objects
    Testimonial:
    pootle flump
    ur codings are working excelent.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Having a Sub-SELECT in the column list of a SELECT statement is ISO standard. The sub-select must return exactly one row with one column (so I wonder how the TOP 937 works here)

    A select without a FROM clause however is non-standard.

    But for the nested (SELECT schema_id) I don't see any advantage (apart from making the statement harder to understand) as schema_id is probably a column from sys.objects.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh I'm not claiming an advantage. It looks like total nonsense. The columns are all from the source table. That isn't the exact query, just an example of the syntax

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I simplified it by converting it into
    Code:
    SELECT  name
          , object_id
          , schema_id
          , type_desc
    FROM    sys.objects
    without changing the result. It returns 92 records on my test system.
    Oh I'm not claiming an advantage.
    Think further! You must have heard of The Obfuscated C and Perl Contests. Now we can have our own Obfuscated MS-SQL Contests ® (OMSC) too! Just think of all the advantages it will bring to be able to write code only at most 2 other people on Earth are able to understand. And then imagine the benefits of one of your developers in your team sharpening his/her OMSC®-skills on production software on a daily base!
    Last edited by Wim; 02-18-11 at 12:19.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oh I have some doozies. I'll start a new thread at the weekend

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by shammat View Post
    The sub-select must return exactly one row with one column (so I wonder how the TOP 937 works here)
    .
    It works because there's only one row to return. It could be TOP {anyPositiveNumber}. That one threw me for a loop at first too.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT  name
          , (object_id)
          , (SELECT schema_id) 
          , (SELECT TOP (937) type_desc)
          , (SELECT TOP (-0) type_desc)
    FROM    sys.objects
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Now where did I put that "facepalm" image macro...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •