Outer Join Type Problem

building semantic layer
1

X-cc: Paul Donovan pdonovan@commercial.fraser-williams.com

Hi,

A colleague who uses Business Objects against a Progress database has asked me for some input on the following problem - I think it would be solved using outer joins somehow, but I only know Oracle - I am told that an Outer Join is not possible in Progress !!

TABLE: PRODUCT TABLE: CONTACTS TABLE: SALES
PRODID | Other fields… PRODID | Other fields… PRODID | SALES
1 1 1 100
2 1 2 300
3 2 3 100
4 3 4 50
5 5 5 80
5

The results required are a list of ALL product id’s and a calculation (the calculation itself
is the count of the contacts for a prodid divided by the number of sales of that product - i.e. for prodid 1 there are 2 records in the contacts table so it would be 2/100)

e.g.

PRODID RESULT
1 0.002 (2/100)
2 0.003 (etc)

The problem is - there will be NO RESULT ROW for PRODID 4 because it doesn’t have a row in CONTACTS - it should still have a result of 0 even though there were no contact records.

Does anyone have any suggestion as to how we can get this to work ? I think we need the functionality of an outer join but without an outer join if you see what I mean !?!?

Thanks in advance,

Paul

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

2

Try solving the problem using two combined queries as follows:

Query 1:
An equi-join (where equal) between (PRODUCT.PRODID=CONTACTS.PRODID) AND (PRODUCT.PRODID=SALES.PRODID)

Example:
SELECT PRODUCT.PRODID, COUNT(CONTACTS.PRODID) / SUM(SALES.SALES) FROM PRODUCT, CONTACTS, SALES
WHERE (PRODUCT.PRODID=CONTACTS.PRODID) AND (PRODUCT.PRODID=SALES.PRODID) GROUP BY PRODUCT.PRODID

The above query will get all products that have contacts and sales.

Query 2:
An exclusion correlated subquery. (NOTE: Progress will need to support sub-queries) Your calculation will always be zero (0) in this case, so just hard code it in the SELECT clause.

Example:
SELECT PRODUCT.PRODID, 0
FROM PRODUCT
WHERE PRODUCT.PRODID NOT IN( SELECT CONTACTS.PRODID FROM CONTACTS) GROUP BY PRODUCT.PRODID

The above query will get all products that have had no contacts.

In BO these two queries can be combined using the “Combine Queries” toolbar icon from the Query Panel window.

My SQL may need some fine tuning, but the concept should work provided Progress supports subqueries.

HTH,
Donald May
maydp@pweh.com

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