TSQL

  • Comments posted to this topic are about the item TSQL

  • 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

  • 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

  • Hi,

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

    1

    2

    3

    4

    5

    6

    Thanks.

  • 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.

    Balachandra Srinevasalu

  • 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.

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

  • 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...

  • What a crap question.

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

  • 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

  • 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.

  • 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 🙂

  • 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.

  • 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.

  • 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 87 total)

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