September 13, 2012 at 11:23 pm
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
September 14, 2012 at 1:07 am
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
September 14, 2012 at 3:16 am
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 14, 2012 at 3:51 am
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.
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
September 14, 2012 at 8:09 am
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........)
September 14, 2012 at 8:48 am
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.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply