TSQL

  • had a little tinker with this one...

    ran script in 2005 to confirm that the question was correct for 2005.

    Then ran the following

    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

    Insert into Test

    Select 'A'

    union Select 'B'

    union Select 'C'

    union Select 'D'

    Select Col

    From (Select Col

    From Test

    Where Isnumeric(Col)=1) X

    Where Col Between 1 and 6

    which returns vals 1 to 6...

    but it also returns an error message

    Msg 245, Level 16, State 1, Line 17

    Conversion failed when converting the varchar value 'A' to data type int.

    why d'you think that is?

  • The crucial point with the conversion error can be demonstrated by trying these statements

    SELECT 1 UNION SELECT 'A'

    and

    SELECT 'A' UNION SELECT 1

    In both cases, you'll get

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

    And you'll get the error whether it's SS 2000 or SS 2005.

    The reason lies in the description of the UNION statement and the rules for data type precedence.

    The following extracts are from SS2000 BOL but the wording in SS 2005 BOL is very similar.

    For the UNION statement,

    When different data types are combined in a UNION operation, they are converted using the rules of data type precedence.

    For the rules of data type precedence,

    When two expressions of different data types are combined by an operator, the data type precedence rules specify which data type is converted to the other. The data type with the lower precedence is converted to the data type with the higher precedence. If the conversion is not a supported implicit conversion, an error is returned. When both operand expressions have the same data type, the result of the operation has that data type.

    This is the precedence order for the Microsoft® SQL Server™ 2000 data types:

    sql_variant (highest)

    ...

    int

    ...

    char

    varbinary

    binary (lowest)

    So the order of the UNION's doesn't matter. It's the precedence of the data types that counts.

    So we have an explanation for the error reported with SS 2005 (and 2008?).

    But one puzzle remains.

    Why doesn't the error occur when the query in the original question is run on SS 2000?

  • gserdijn (3/26/2009)


    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)

    You're quite right. The data type isn't determined by the first value, it's determined by data type precedences

    see here for SQL2008 http://msdn.microsoft.com/en-us/library/ms190309.aspx

    here for SQL 2005 http://msdn.microsoft.com/en-us/library/ms190309(SQL.90).aspx

    int beats varchar hands-down everytime!

    Kev

  • I took so long to submit that post that dmw beat me to it!

    Kev

  • I agree that the version should be stated in the question.

    This works fine in 2000 so long as you create the table defining the column as varchar. To get the correct answer you need to allow the first entry to define the data type

    Select 1 col

    into Test

    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'

    will produce the desired result and have gotten us all the deserved points

  • I agree with others, you need to post a SQL version number since results will vary. Older versions of MS-SQL return 1,2,3,4,5,6.

    Steve

  • RichardB (3/26/2009)


    What a crap question.

    Plain wrong, in fact.

    That's two "clunkers" in the last 2 weeks. I answered the question correctly, from a SQL 2000 standpoint, and was told it was incorrect. Ran it through QA and it confirmed my answer was indeed correct, for my verison (2K, sp4). What's going on? As stated in earlier posts, if it's version specific, please let us know upfront. Typically, for 2008 questions, I pass on them because I know little about the changes in the version and rely on the various forums to provide insight that I cannot otherwise obtain since I don't work with it - yet.

    -- You can't be late until you show up.

  • Hi,

    When i running the below query,

    Select Col From (Select Col From Test Where Isnumeric(Col)=1) X Where Col Between 1 and 6

    Output is: 1,2,3,4,5 and 6

    Still my answer is wrong....? why ?

  • In SQL 2005, it returns both the 1-6 option, and gives the conversion error for me.

    The Redneck DBA

  • Can you plz explain, why the error comming ?

  • Now here's the weird thing. When I run this (on SQL Server 2005):Create Table #Test(col varchar(10))

    go

    Insert into #Test

    Select cast(1 as varchar(10))

    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'

    Select Col

    From (Select Col

    From #Test

    Where Isnumeric(Col)=1) X

    Where Col Between 1 and 6

    go

    Drop table #Test I get the exact same error!?!

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • I guess the moral of the story is do not rely on implicit type conversions.

  • While the question and answer were not ideal, this question was valuable to me because I learned so much from the discussion that followed.

  • So given question is wrong , compare to your queries:

    If you run the below queries the ans is: 1,2,3,4,5, and 6.

    If I am correct.....

    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'

    Select Col From (Select Col From Test Where Isnumeric(Col)=1) X Where Col Between 1 and 6

  • my 2005 also gave the error, matching the "correct" answer. 9.0.3073 (SP2) 64-bit and 9.0.3310 (SP2 w/CU11) 32-bit

    EDIT: On both versions I only got the error message. I did not get a result set.

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

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