TSQL

Question

TSQL

gyanp.garg

SSC Veteran

Points: 274
More actions
March 25, 2009 at 8:17 pm

#219427

Comments posted to this topic are about the item TSQL

Viewing 15 posts - 1 through 15 (of 87 total)

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

Marius Els Right there with Babe Points: 763 More actions · Answer 1

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

gserdijn Hall of Fame Points: 3290 More actions · Answer 2

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

calldebraj SSC Rookie Points: 39 More actions · Answer 3

Hi,

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

1

2

3

4

5

6

Thanks.

Balachandra SSCrazy Points: 2606 More actions · Answer 4

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

arconen Old Hand Points: 312 More actions · Answer 5

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.

Mighty SSCrazy Eights Points: 9063 More actions · Answer 6

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

Marius Els Right there with Babe Points: 763 More actions · Answer 7

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

RichB SSCrazy Eights Points: 9651 More actions · Answer 8

What a crap question.

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

karthik M One Orange Chip Points: 29139 More actions · Answer 9

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

Tao Klerks SSCarpal Tunnel Points: 4297 More actions · Answer 10

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 Ten Centuries Points: 1051 More actions · Answer 11

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 Right there with Babe Points: 778 More actions · Answer 12

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 Right there with Babe Points: 778 More actions · Answer 13

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 Ten Centuries Points: 1288 More actions · Answer 14

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.