Re: SQL-92 Outer Join support (lack)

Mike,

The literal qualifier must be in the WHERE cause. The trick with an outer join is keeping it from suppressing the unmatched rows. This can be accomplished with:

> SELECT PERSON.NAME, PHONE.TYPE, PHONE.NUMBER
> FROM PERSON LEFT OUTER JOIN PHONE ON
> PHONE.SSN = PERSON.SSN
> WHERE
> PERSON.STATUS = 'ACTIVE'
> AND
> NVL(PHONE.TYPE,'HOME') = 'HOME'
Ugly, but try it; you'll like it.

Have fun out there,
Gary

Mike Rowland wrote:
>
> 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?
>
> 1. A left join that includes a literal selection?
>
> SQL-92 example for "HOME" phone number:
> ---------------------------------------
> SELECT PERSON.NAME, PHONE.TYPE, PHONE.NUMBER
> FROM PERSON LEFT OUTER JOIN PHONE ON
> PHONE.SSN = PERSON.SSN AND
> PHONE.TYPE = 'HOME'
> WHERE
> PERSON.STATUS = 'ACTIVE'
>
> ORACLE 7.3.3 EQUIVALENT? (Without using a view like "HOME_PHONE")
>
> 2. Needing to list all a PERSON's available information
> SQL-92 example:
> ---------------------------------------
> SELECT PERSON.NAME, PHONE.NUMBER, ADDRESS.CITY, DEPENDANT.NAME
> FROM PERSON LEFT OUTER JOIN PHONE ON
> PHONE.SSN = PERSON.SSN
>
> --
> *****************************************************************
> * The opinions expressed are solely the opinion of Mike Rowland *
> * and not those of Cap Gemini America. *
> *****************************************************************
  Received on Fri Oct 17 1997 - 00:00:00 CEST