TSQL

  • gyanp.garg

    SSC Veteran

    Points: 274

    Comments posted to this topic are about the item TSQL

  • Marius Els

    Right there with Babe

    Points: 760

    Hi there

    I work in a SQL 2000 developer edition. when I run the provided script, it runs successfully returning rows 1 through 6.

    Only if I run the script, without the from subselect, does it return a conversion error.

    Why is that?

    Marius

  • gserdijn

    Hall of Fame

    Points: 3266

    When the SELECT statement is compiled for the INSERT, the first value is implicitly taken as an INT. In the UNIONs, as each SELECT is added, they must match this data type.

    If that is the correct explanation, would the next statement work?

    Insert into test Select 'A' union Select 1;

    (Answer: No)


    Dutch Anti-RBAR League

  • calldebraj

    SSC Rookie

    Points: 39

    Hi,

    The answer given here is wrong.The correct answer will be

    1

    2

    3

    4

    5

    6

    Thanks.

  • Balachandra

    SSCrazy

    Points: 2595

    Hi

    The given script will work in SQL Server 2000 giving the results 1,2,3,4,5,6.

    In SQL Server 2008 it fails saying conversion failed when converting the varchar value 'A' to data type int.

  • arconen

    Old Hand

    Points: 312

    In future must show the version of MS SQL Server in order to prevent a misunderstanding.

    I answered right for version 2000, but for 2008 I made a mistake.

  • Mighty

    SSCrazy Eights

    Points: 8814

    On SQL 2005 it also works, so in that case answer c is the correct one.

  • Marius Els

    Right there with Babe

    Points: 760

    I'm glad to see my thinking (from a SQL 2000 point of view) was at least in line with some other folks as well.

    Unfortunately this didn't get us the points :crying:

    I agree: indicating the version of SQL the script was written in would have helped...

  • RichB

    SSCrazy Eights

    Points: 9651

    What a crap question.

    Can someone adjust this, its pretty bad. Plain wrong, in fact.

  • karthik M

    One Orange Chip

    Points: 29139

    I ran this script it in sql2000. it is working fine. it shows 1,2,3,4,5,6. so the correct answer is c.

    karthik

  • Tao Klerks

    SSCarpal Tunnel

    Points: 4297

    Guys, rather than repeatedly lamenting how terrible it is that you got the question "wrong" (as did I) even though it works in SQL 2000 / 2005, can anyone explain why it works???

    if you run the following, then you get an error:

    Select 1

    union Select 2

    union Select 3

    union Select 4

    union Select 5

    union Select 6

    union Select 7

    UNION Select 'A'

    union Select 'B'

    union Select 'C'

    union Select 'D'

    Server: Msg 245, Level 16, State 1, Line 1

    Syntax error converting the varchar value 'A' to a column of data type int.

    That is normal / expected SQL Server behaviour. I believe the first result set in the UNION defines the data type that will be used for the rest of the UNION statement, but there is probably more information in the link attached to the question.

    Now as soon as you add an INSERT (to a table with appropriate type) in SQL 2000, the error goes away:

    Create Table Test(col varchar(10))

    GO

    Insert into Test

    Select 1

    union Select 2

    union Select 3

    union Select 4

    union Select 5

    union Select 6

    union Select 7

    UNION Select 'A'

    union Select 'B'

    union Select 'C'

    union Select 'D'

    (11 row(s) affected)

    Is this because of some compiler optimization?? Logically, you would expect the UNION clauses to be evaluated first, any duplicates removed, and then afterwards convert the resulting single resultset into the necessary types for insert into the destination table - but somehow the destination type is being used during the evaluation of the UNION clauses.

    Does anyone have details on how this happens?

    http://poorsql.com for T-SQL formatting: free as in speech, free as in beer, free to run in SSMS or on your version control server - free however you want it.

  • GlenParker

    SSC Eights!

    Points: 941

    Like all 2000 users, I voted for option 'C' and got it 'wrong'

    Would it be possible for future QotD to be validated in 2000, 2005 & 2008 before submission.

    As a relatively new boy on the block, with relatively meager points, I do feel relatively cheated of what I thought were 2 points in the bag.

    Glen Parker 🙂

  • ashok-831066

    Right there with Babe

    Points: 778

    Hi

    The correct answer is C that is

    1

    2

    3

    4

    5

    6

    I run this script in sql server 2005 and check it. Its not give an error. Its give an output.

    Please check it.

  • ashok-831066

    Right there with Babe

    Points: 778

    Hi,

    Suppose if your query will be like this ie (Col='A') instead of (Col=1)

    Select Col

    From (Select Col

    From Test

    Where Isnumeric(Col)='A') X

    Where Col Between 1 and 6

    You can get the error that is syntax error converting the varchar value 'A' to a column of data type int.

  • Martin Wills

    Ten Centuries

    Points: 1288

    Are you sure Ashok? I ran the query with SQL 2005 and got the conversion error.

    One thing to check is that it's the version of the server software that matters not the version of the client software.

    In other words, running SSMS 2005 when the server is SQL 2000 means that you won't get the error.

Viewing 15 posts - 1 through 15 (of 88 total)

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