Re: URGENT! Need help with SQL*Plus syntax

You could do a quick fix by using a UNION statement. Use the first query to retrieve all those records with a correspondance address, and then a second to retrieve all those that only have a property address. Your outer joins would appear twice, but once in each query.

Helen

RBurt72685 wrote in message
<19990310054953.09892.00000440_at_ng-fa1.aol.com>...
>I am trying to join three tables with the following logic but the syntax
>escapes me. Can anyone help please.
>
>There are three tables. An account table including the property address, a
>correspondance address table (joined to the account table by an account
number)
>and a Post Office Walksort code table. The third table can be joined to
either
>of the first two by matching Post Codes.
>
>For each account I need to print a bill which must be addressed to the
>correspondance address if there is one or to the property address if there
>isn't. The correct walksort code needs to be pulled out from the third
table.
>
>The logic for this is fairly simple, as follows:
>.....
>and walksort.postcode = nvl(correspondance.postcode, account.postcode)
>......
>
>However, there is *not* always a vaild entry in the walksort table for
every
>postcode. In that case I need to print a bill addressed to the
correspondance
>address if there is one and the property address if there isn't but without
>showing a walksort code on the bill. The above code does not print anything
at
>all where the walksort postcode cannot be matched against either of the
other
>two postcodes.
>
>I tried using an outer join (ie.walksort.postcode(+) =
>nvl(correspondance.postcode, account.postcode)) but SQL*Plus will not let
me
>outer join a table to more than one other table.
>
>Can anyone help please?
>
>
>Richard
>
Received on Thu Mar 11 1999 - 00:02:08 CST