Now then, Oracle 9i can use outer-join syntax in the FROM clause (previous Oracle versions could not). But the Oracle 9i outer-join syntax seems to require that one knows whether or not a left or right outer join is required – the three options are RIGHT OUTER JOIN, LEFT OUTER JOIN, and FULL OUTER JOIN. Is there some way I can tell whether the word “OUTER” in the Informix SQL above is signifying a left, right, or full outer join?
This document talks about converting Informix to Microsoft SQL Server but the information about Informix should be useful to Oracle as well.
Search for “Join Clauses” to find the section that talks about joins. The left outer join section says:
This type of join specifies that all of the left table outer rows be returned, even if no column matches are found. This operates just like an Informix outer join.
The sections about right and full outer joins says there is no equivalent in Informix. I assume this means previous versions of Informix that didn’t support ANSI syntax.
Hmmm …when I thought about it later, it seemed that it might have been useful to actually break down the Informix WHERE clause, and see how the tables.columns are ordered in each X.x = Y.x statement (if that makes sense).
See, I’m used to Oracle versions up to 8i, and it’s 9i that seems to allow the ANSI join syntax (working on a 9i db now). I can define the joins in Oracle 9i the way I would have in previous versions, but I am having trouble “unraveling” the Informaix code. Therefore, I was hoping it would be easier to just go ahead and define the joins in the FROM clause … but I’m having trouble figuring out just what the Informix FROM clause above is telling me.
If I really can make 'em all left outer joins, then life becomes much easier.
Without knowing the data structure, I’m taking a shot in the dark, but this might get you started.
Select SVCORD_SYSTEM, NSPS_SYSTEM, PROVIDER PROV1, SVCORD_CIRCUIT, CIRCUIT, PROVIDER,
LOCATION, ADDRESS, OFFNET_DESC, CUSTOMER, Contact, SERVICE_ADDRESS,MACD_SVCORD,
MACD_WORKORDER, CUSTOMERACCOUNT, BILLINGACCOUNT
FROM SVCORD_LOC_SVC, NC_REF_DEC
Where SVCORD_SYSTEM (+)= NSPS_SYSTEM
and PROVIDER PROV1(+)=PROVIDER
And SVCORD_CIRCUIT (+)=CIRCUIT
and LOCATION(+)=ADDRESS
and CUSTOMER(+)=Contact
and MACD_SVCORD(+)=MACD_WORKORDER
and CUSTOMERACCOUNT(+)=BILLINGACCOUNT
Thanks for the effort, paperbackwriter. In this case, though, the Informix FROM statement plays the same role as an Oracle FROM statment. In the OP, I left the SELECT statement out because there was no question about it – it’s pretty straightforward.
… SCSimmons, parallax – if I’m following you two correctly, the following statements should be equivalent:
Informix SQL:
FROM table1 OUTER table 2
WHERE table1.column1 = table2.column1 Oracle (8i or earlier) SQL:
FROM table1, table 2
WHERE table1.column1 (+) = table2.column1
or
FROM table1, table 2
WHERE table2.column1 = table1.column1 (+) Oracle 9i SQL:
FROM table1 OUTER LEFT JOIN table 2
WHERE table1.column1 = table2.column1
or
FROM table2 OUTER RIGHT JOIN table 1
WHERE table2.column1 = table1.column1
Sadly, I know Informix SQL much better than Oracle, which I don’t usually have to work with. But assuming Oracle 9i uses basically standard outer join syntax, I’d say you’ve got it. (And I gleefully walk away clutching the Oracle 8i outer join syntax, which I’d never taken the time to research. )
)