Easiest way of asking the question is to give an example …
Relationships of tables :
Customer --(one to zero or many)-- Customer Product —(zero or many to one)
Product
Queries possibly needing to be run :
- Listing of all Customers and products whether or not they have bought a
product - Listing of all Products and Customers whether or not the product has been
purchased by a customer
Problem :
The two above queries require outer joins to be defined but differently for
each case. eg for the queries the ‘Where’ clauses would need to be
-
( PROD_CUST.CUST_CD(+)=CUST.CUST_CD ) AND (
PROD.PROD_CD(+)=PROD_CUST.PROD_CD ) -
( PROD_CUST.CUST_CD=CUST.CUST_CD(+) ) AND (
PROD.PROD_CD=PROD_CUST.PROD_CD(+) )
Is the only solution creating an alias of the Customer Product table with
each of the outer join ‘routes’ defined in different contexts? I have
tested this option and it works although it could be a real pig to do on a
large universe with a lot of this type of structure where you users need to
drill from ‘both ends’.
p.s database is Oracle 7.3.3 and BusObj 4.1.4
Regards
Jonathan
Project Leader
Management and Commercial Systems
Global Medical, Regulatory and Product Strategy (GMRPS) IS
Listserv Archives (BOB member since 2002-06-25)