March 26, 2009 at 4:11 am
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?
March 26, 2009 at 4:17 am
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?
March 26, 2009 at 4:32 am
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
March 26, 2009 at 4:34 am
I took so long to submit that post that dmw beat me to it!
Kev
March 26, 2009 at 5:02 am
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
March 26, 2009 at 6:01 am
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
March 26, 2009 at 6:45 am
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.
March 26, 2009 at 6:46 am
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 ?
March 26, 2009 at 6:57 am
In SQL 2005, it returns both the 1-6 option, and gives the conversion error for me.
The Redneck DBA
March 26, 2009 at 7:02 am
Can you plz explain, why the error comming ?
March 26, 2009 at 7:13 am
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]
March 26, 2009 at 7:21 am
I guess the moral of the story is do not rely on implicit type conversions.
March 26, 2009 at 7:32 am
While the question and answer were not ideal, this question was valuable to me because I learned so much from the discussion that followed.
March 26, 2009 at 7:47 am
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
March 26, 2009 at 7:54 am
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 87 total)
You must be logged in to reply to this topic. Login to reply