Re: SQL-92 Outer Join support (lack)

Hello, Mike.

Mike Rowland <102622.2522_at_CompuServe.COM> wrote in article <efbXpUe28GA.186_at_ntdwwaaw.compuserve.com>...
> I have recently moved back to using Oracle 7.3 at a client. I
> have grown accustomed to using the SQL-92 syntax for outer joins.
> Oracle 7.3 has several limitations that prevent these from
> working. Does anyone have "good" work arounds for these
> questions?

The operator "(+)" is the outer join operator at least since Oracle6. Funny, I didn't know the words "OUTER JOIN" or "INNER JOIN" were from SQL-92 syntax. (are they?)

Anyway, the answer for your question is to rewrite our SQL commands. I think they should be like below. I believe that there are PERSONs without PHONE. In this case, you have to trick the command so a "phantom phone number" is given to persons who doesn't have one. It is like the PHONE table had one more row, with a "generic" phone number.

> 1. A left join that includes a literal selection?
> SQL-92 example:
> ---------------------------------------
> SELECT PERSON.NAME, PHONE.TYPE, PHONE.NUMBER
> FROM PERSON, PHONE
> WHERE
> PHONE.SSN(+) = PERSON.SSN AND
> PHONE.TYPE = 'HOME' AND
> PERSON.STATUS = 'ACTIVE'

> 2. Needing to list all a PERSON's available information
> SQL-92 example:
> ---------------------------------------
> SELECT PERSON.NAME, PHONE.NUMBER,
> ADDRESS.CITY, DEPENDANT.NAME
> FROM PERSON, PHONE
> WHERE
> PHONE.SSN (+) = PERSON.SSN
I hope this works.

Danilo Gimenez
danilog_at_mandic.com.br
Sao Paulo - SP - Brasil Received on Fri Oct 17 1997 - 00:00:00 CEST