Oracle statement versus SQL

[rstefano]

We have a table associated with Opportunity that contains 2 date fields. The row itself is *sometimes* present (sometimes they fill in the dates, sometimes not), so I have to do an outer join (Oracle) aka a left join (Sql Server).
The "left join" syntax doesn't work on Oracle, and the "(+)" outer join syntax doesn't work on SqlServer:

Oracle Syntax:
Select SALESPOTENTIAL,ACTUALAMOUNT, STATUS, a.CREATEDATE, a.ESTIMATEDCLOSE, a.ACTUALCLOSE, c.TARGETED_DECISION_DATE, c.actual_decision_date from OPPORTUNITY a, C_OPPTDECISIONDATES c where a.OPPORTUNITYID = 'O6UJ9A000000' and a.opportunityid = c.opportunityid(+)


SqlServer syntax:
Select SALESPOTENTIAL,ACTUALAMOUNT, STATUS, a.CREATEDATE, a.ESTIMATEDCLOSE, a.ACTUALCLOSE, c.TARGETED_DECISION_DATE, c.actual_decision_date from OPPORTUNITY a
left join C_OPPTDECISIONDATES c on a.opportunityid = c.opportunityid where a.OPPORTUNITYID = 'O6UJ9A000000'


How does one write code that works on both databases?

[skhanal]

Use ANSI standard SQL,

select * from
OPPORTUNITY a
left outer join C_OPPTDECISIONDATES c on a.opportunityid = c.opportunityid where a.OPPORTUNITYID = 'O6UJ9A000000'

[rstefano]

That was the same statement as the MS SQL one that I posted. That doesn't work on Oracle.

[skhanal]

What version of Oracle are you using. LEFT OUTER JOIN is supported in Oracle 9i.

[rstefano]

We're using Oracle 8.17

[john_burgess]

The way we get around this sort of problem is to create a view in both databases that does the outer join,
using left outer join in sql server
and (+) in Oracle.
Your code then selects from the view.

ie.
Create or Replace view ggg as
Select SALESPOTENTIAL,ACTUALAMOUNT, STATUS, a.CREATEDATE, a.ESTIMATEDCLOSE, a.ACTUALCLOSE, c.TARGETED_DECISION_DATE, c.actual_decision_date,
a.opportunityid
from OPPORTUNITY a, C_OPPTDECISIONDATES c where a.opportunityid = c.opportunityid(+);

then select salespotential,...
from ggg where
oportunityid='O6UJ9A000000'

[rstefano]

Thanks I will give that a try