Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> URGENT! Need help with SQL*Plus syntax
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 Wed Mar 10 1999 - 04:49:53 CST