Outer joins from 2 directions

building semantic layer
1

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 :

  1. Listing of all Customers and products whether or not they have bought a
    product
  2. 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

  1. ( PROD_CUST.CUST_CD(+)=CUST.CUST_CD ) AND (
    PROD.PROD_CD(+)=PROD_CUST.PROD_CD )

  2. ( 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)

2

Vasan,

    >Of course, DAS would re-generate the SQL, when ever the universe is

imported
>from repository.
Unless they have fixed this in 4.1.4 you will find that if the SQL for an
object is modified in the universe and the universe is reexported, although
the DAS reimports it the SQL is NOT regenerated i.e the SQL remains the same
as it was. I had a call logged with BusObj about this but I haven’t checked
it recently.

Regards

Jonathan

Project Leader
Management and Commercial Systems
Global Medical, Regulatory and Product Strategy (GMRPS) IS

Listserv Archives (BOB member since 2002-06-25)