March 26, 2009 at 8:23 am
This works on both SQL 2000 and 2005 and produces answer (c). Please amend the question to be more specific about SQL versions.
Ganesh
March 26, 2009 at 8:29 am
ganeshi (3/26/2009)
This works on both SQL 2000 and 2005 and produces answer (c). Please amend the question to be more specific about SQL versions.Ganesh
I have tested it on SQL Server 2005 and I get answer (a).
[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 8:32 am
RBarryYoung (3/26/2009)
ganeshi (3/26/2009)
This works on both SQL 2000 and 2005 and produces answer (c). Please amend the question to be more specific about SQL versions.Ganesh
I have tested it on SQL Server 2005 and I get answer (a).
Are you looking at both tabs in SSMS (Results and Messages)? I got the "correct" result, AND the error message, just in different tabs.
The Redneck DBA
March 26, 2009 at 8:49 am
I have opened seven tabs and runned the same query in SQL2000
" Select Col From (Select Col From Test Where Isnumeric(Col)=1) X Where Col Between 1 and 6 "
Still the answer is : 1,2,3,4,5 and 6
Needs to replace question or change the answer.
March 26, 2009 at 8:53 am
I have edited the question to say SQL 2005. We don't necessarily have time to test and go through all questions on all platforms, and I should have noted I tested this on 2005, where the answer and explanation match.
I have also awarded back points to people for this question to this time.
March 26, 2009 at 8:55 am
Jason Shadonix (3/26/2009)
RBarryYoung (3/26/2009)
ganeshi (3/26/2009)
This works on both SQL 2000 and 2005 and produces answer (c). Please amend the question to be more specific about SQL versions.Ganesh
I have tested it on SQL Server 2005 and I get answer (a).
Are you looking at both tabs in SSMS (Results and Messages)? I got the "correct" result, AND the error message, just in different tabs.
I tested it again in S2K sp4. The grids tab show 1,2,3,4,5,6. The messages tab shows: (11 row(s) affected) --the inserted rows and
(6 row(s) affected) -- the returned rows. No errors at all.
-- You can't be late until you show up.
March 26, 2009 at 8:57 am
Thanx. That is the spirit. I think this topic will ends here.
March 26, 2009 at 9:13 am
For Sql Server 2000 and Sql Server 2005 (with Compatibility mode 80) you can insert into table.
But the select fails with the said error.
Sql Server 2005 (with Compatibility mode 90), I got an error from the INSERT statement.
March 26, 2009 at 9:31 am
Steve Jones - Editor (3/26/2009)
I have edited the question to say SQL 2005. We don't necessarily have time to test and go through all questions on all platforms, and I should have noted I tested this on 2005, where the answer and explanation match.I have also awarded back points to people for this question to this time.
The explanation is still incorrect. The error returned is due to datatype precedence and NOT the datatype of the first value in the SELECT / UNION. As noted by RBarryYoung in post 684098.
http://www.sqlservercentral.com/Forums/FindPost684098.aspx
The following still fails when the first entry is VARCHAR:
Insert into Test
Select 'X'
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'
March 26, 2009 at 9:52 am
It is the explanation that is wrong. The values are inserted into the table, just fine.
The error occurs when 'A' is tested Between 1 and 6 in the WHERE clause.
This is about processing order, and clearly the WHERE clause is being processed before the select statement in the FROM clause which restricts the set to numeric values only.
March 26, 2009 at 9:54 am
In SQL 2008, the error is produced by the Between clause, not the initial Insert and Unions.
The Help states the expressions used in the Between statement must all be the same type.
"Select COL From test Where COL Between 1 and 6" produces an error.
"Select COL From test Where COL Between 'A' and 'D'" is successful.
This was a poor question because the SQL Version wasn't stated, and even the explanation was incorrect!
March 26, 2009 at 10:10 am
The following works without errors in SQL 2005. The table is created, the values inserted and the results selected.
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 Test
Where Isnumeric(Col)=1
And Col Between 1 and 6
March 26, 2009 at 10:18 am
John Kraeck (3/26/2009)
The following works without errors in SQL 2005. The table is created, the values inserted and the results selected.
Not in SQL Server 2005 SP3
March 26, 2009 at 10:25 am
I ran the script in 2005 and got both a result set of 1 to 6 AND the error that was noted. Not what I expected, but since the question only allowed one answer, I opted to answer the error.
March 26, 2009 at 10:28 am
I do not know which sp3 you are running but it worked just fine on mine
SELECT Serverproperty('Edition') as Edition,
Serverproperty('EngineEdition') as Engine,
Serverproperty('ProductVersion')as Version,
Serverproperty('ProductLevel') as upgrade
returns...
Developer Edition39.00.4035.00SP3
...and since "Test" still exists,
SELECT Col
FROM Test
WHERE ISNUMERIC(Col) = 1
AND Col BETWEEN 1 AND 6
gives the results 1 through 6 and the message (6 row(s) affected)
Viewing 15 posts - 31 through 45 (of 87 total)
You must be logged in to reply to this topic. Login to reply