SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


TSQL


TSQL

Author
Message
Tao Klerks
Tao Klerks
SSCommitted
SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)SSCommitted (1.5K reputation)

Group: General Forum Members
Points: 1537 Visits: 1249
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
GlenParker
Right there with Babe
Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)Right there with Babe (773 reputation)

Group: General Forum Members
Points: 773 Visits: 133
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
ashok-831066
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 84
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
ashok-831066
SSC Journeyman
SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)SSC Journeyman (84 reputation)

Group: General Forum Members
Points: 84 Visits: 84
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
Martin Wills
Right there with Babe
Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)

Group: General Forum Members
Points: 716 Visits: 893
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.
Ben Leighton
Ben Leighton
Say Hey Kid
Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)Say Hey Kid (662 reputation)

Group: General Forum Members
Points: 662 Visits: 359
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?
Martin Wills
Martin Wills
Right there with Babe
Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)Right there with Babe (716 reputation)

Group: General Forum Members
Points: 716 Visits: 893
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?
kevriley
kevriley
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3124 Visits: 2618
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
kevriley
kevriley
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3124 Visits: 2618
I took so long to submit that post that dmw beat me to it!



Kev
jritson
jritson
SSC Eights!
SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)SSC Eights! (875 reputation)

Group: General Forum Members
Points: 875 Visits: 295
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search