Outer Join Problem

[timida]

Hello!

I have a problem with an outer Join and a drill through report. I want to show all products from one group with the price.
In the first report I select the group and a price (there are about 10 different prices). The drill through report should show all products in the group even if they don't have assigned the specific price.

For example (simplified SQL):
select product.nr
from product, price
where product.nr = price.product_nr
and price.dom_price = 2

The problem is that there are products in one group which don't have all prices defined in the table price (for example: standard, plan, budget price).

When I drill to this report whith the price standard (nr=2). One product is not displayed because it doesn't have an entry in the table price for the standard price only entries for other prices.

In the SQL I only need to define an outer join to "price.dom_price (+) = 2" and it works. The problem is that I don't know how to do this in impromptu (this condition is generated by the drill through).

I also tried the solution described in thread401-330981 - but this doesn't work for me as it only lists products which don't have an entry in the table price.

Thanks in Advance!

 

[GilMerc]

Hi timida,

I think the solution in thread 401-330981 could be work's. If not you must define the join between table in impromptu and define a outer join for product table.

If it's not working again try this filter if you want to show anytime the product without price :
1 = if (product.nr = ?prompt?) then (1) else if (price is missing) then (1) else (0)

 

[griffindm]

Gil,

For thread references, be sure to remove any spaces in the ref, as in thread401-330981.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[timida]

Hi,

The problem is that I need the outer join in the drill-through condition (nr=2 --> nr(+)=2).

And the solution of Thread401-330981 only shows products which have no entries in the table price. However I want to see all products from one group - regardless of the price and the entries of the table price. But when there is an entry for the selected price this price should be used.

For example:
(Note: one product can be in one or more groups and one group can contain one or more products)

Table product
Nr   Name
1    Pro one
2    Pro two
3    Pro three
4    Pro four

Table dom_price
Nr   Name
1    Standard Price
2    Budget Price
3    Plan Price
...

Table price
Nr   Product_nr   dom_price   Value
1    1            1           10
2    1            2           11
3    2            2           9
4    2            3           11
5    3            1           15

Group A contains Product 1, 2 and 3

When I select Group A and price 2 (budget price), I want to see product 1, 2 and 3!. Now I only see one and two because they have defined the selected price (budget) in the table price.

Regarding your filter:
I don't have a prompt and I don't know how to use this condition.

Thanks

 

[GilMerc]

Hi,

First, how do you choose a group?

Otherwise, I think it's possible to do what you want if you create a join between tables PRODUCT and PRICE where PRODUCT has an external join and create a join between DOM_PRICE and PRICE where DOM_PRICE has an external join.

I'm not take an test but I think if you choose PRODUCT 1,2,3 and choose 2 for the DOM_PRICE, the report you will give you
PRODUCT 1 -> VALUE 11
PRODUCT 2 -> VALUE 9
PRODUCT 3 -> VALUE -

Try this, if it dosn't work I will take a test.

Gilles.

 

[griffindm]

timida,

The Impromptu drill-thru filters are inner-join with equi-join conditions only. You cannot make an explicit outer-join or change from an equi-join to another boolean (such as <=, >=, etc).

What you CAN do, is to make the drill-thru report point into a view that does the outer-join for you. Put all the columns you need for your drill-thru report into the view, then add the view to your catalog and port your drill-thru report to the new catalog sources. It should work fine and show all products, whether or not they have a row in the price table.

Regards,

Dave Griffin


The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;
Want good answers? Read FAQ401-2487 first!

http://www.decisionsupportgroup.com

 

[timida]

thanks for your help...

as my situation is more complex than I described I will try the solution with the view.

timida