subqueries in sql server2000

Question

Post reply

subqueries in sql server2000

raghuldrag

Hall of Fame

Points: 3160
More actions
September 13, 2012 at 11:23 pm

#261999

Hai friends ,
i ve one doubt in subqueries in oracle
select col1,col2 from @tab1 where(col2,col3) in(select col2,min(col3) from @tab1 group by col2)
while i am executing these query gives output.....
as the same query em executing in sql server 2000 show error on place of where....
what is the problem please explain me

Viewing 6 posts - 1 through 5 (of 5 total)

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

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 1

A better way to write that query is

Select

Col1

,Col2

From

@tab1 t1

INNER JOIN (Select Col2,Min(Col3) Col3

FROM @tab1

group by Col2) sc1

on t1.Col2=sc1.Col2

AND t1.Col3=sc1.Col3

This should perform as per the oracle query and give the same results.

Its just one of those Oracle vs MSSQL T-SQL quirks that exists. I cant offer an explination as I dont know enough about Oracle to comment on it.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Phil Parkin SSC Guru Points: 246985 More actions · Answer 2

raghuldrag (9/13/2012)
Hai friends ,
i ve one doubt in subqueries in oracle
select col1,col2 from @tab1 where(col2,col3) in(select col2,min(col3) from @tab1 group by col2)
while i am executing these query gives output.....
as the same query em executing in sql server 2000 show error on place of where....
what is the problem please explain me

Wrong forum again...

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

raghuldrag (9/13/2012)
Hai friends ,
i ve one doubt in subqueries in oracle
select col1,col2 from @tab1 where(col2,col3) in(select col2,min(col3) from @tab1 group by col2)
while i am executing these query gives output.....
as the same query em executing in sql server 2000 show error on place of where....
what is the problem please explain me

Oracle and SQL Server both have some proprietary syntax.

where (col2,col3) in...

is not SQL Server syntax.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

bala.a SSC Enthusiast Points: 153 More actions · Answer 4

Wrong syntax.....

You need to use both the columns seperately in the WHERE clause

Like this..

WHERE COL1 IN (SELECT COL1 FROM.....)

AND COL2 IN(SELECT COL2 FROM........)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

bala.a (9/14/2012)
Wrong syntax.....
You need to use both the columns seperately in the WHERE clause
Like this..
WHERE COL1 IN (SELECT COL1 FROM.....)
AND COL2 IN(SELECT COL2 FROM........)

Actually, to emulate the multiple column IN syntax of Oracle you need to use the INNER JOIN that Jason-299789 provided with the subquery as a derived table in the FROM clause.