Old style Oracle Join, convert to SQL Server?? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Old style Oracle Join, convert to SQL Server??

Author

Topic

jameswen
Starting Member

2 Posts

Posted - 2014-09-24 : 11:23:45

I am work on convert to SQL Server from Oracle. Find old style join, trying to convert. Any help?

Here is trouble code from Oracle:

select <anything>
from a,b,c,d,e
where a.x = b.x (+)
and c.y = d.y (+)
and 1234 = e.z (+)

I rewrite for SQL Server and get:

select <anything>
from a left outer join b on a.x = b.x ,
c left outer join d on c.y = d.y,
where (select e.z where e.z = 1234)

This is not correct and gives errors. Last line I do not know what to do?? Any ideas??

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-09-24 : 12:24:18

Query seems a little sparse, not fully joined. Here's the best I can do to translate it as it is now:



select anything
from a 
left outer join b on a.x = b.x
cross join c
left outer join d on c.y = d.y
left outer join e on e.z = 1234

jameswen
Starting Member

2 Posts

Posted - 2014-09-24 : 14:50:50

Thank You!! I do not follow the cross join here though. Wouldnt that give me all rows where I am only after rows that are equal?

The last line also needs something. What I want for the last line would be:

e left outer join 1234 on 1234 = e.z

But.. this is not correct syntax. I need to somehow get all rows from table e field z that equal 1234. The original Oracle does not put this in a select statement. Rather, it does the left outer join and I do not follow why. Maybe no real reason. I think if I put in the select it will change results. I also do not see a way to do this with a join since I do not have two tables. Any ideas? thanks.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2014-09-26 : 10:59:45

1234 is not a table, it's a value (at least that's what it looks like to me).

Since there is no connection in the main query from a/b to c/d, I assumed it would be a CROSS JOIN / Cartesian product, as that is the default in SQL if no join is used. But I may be wrong, as I'm not an expert in Oracle queries, only in SQL Server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources