dynamic join

[cognosfreelancer]

I have a problem that is similar to a run time versus design time change.

Assume I have three aliases, REGION,ZONE,AREA

I have a fact table that contains a type field that I can use with decode to determine whether the row shoud be joined to a REGION or  ZONE or AREA.

Can I have such a conditional join. In design time I will have to have all three joins in place.

While running the query only one of the three joins should be activated depending on the row being retrieved.

Note that the other joins should not have any effect.

This seems difficult since what I have at design time is a loop join.

NKT

[CoginAustin]

The fact table contains a key that is a key to one of three tables?


You can do dynamic joins, I have done them, but I do not think I would let this go into production mode like this. It seems the fact table or dimension(or both) needs to be changed in order to do it correctly.

If this is incorrect please provide additional info.

[cognosfreelancer]

No that is not true.

The table that needs to be joined to one of the three tables has a field caled area_type_code. This field will contain a flag indicating what kind of a row it is , whether it is a region, zone etc.

The actual join is based on a surrogate key called area id.

NKT

[T]

I did something similar about a year ago.

We had summary tables held at day, month and year levels (called sales_day, sales_month and sales_year).  We set up a join between the summary tables and the relevant dimensions based using a variable, depending on the data required by the report.

The joins were defined as "sales_<date_variable>.<dim1_id> = <dim1_table.dim1_id>", where <date_variable> could have values of day, month or year, depending on a parameter value passed from the report.  From memory, the datatype of the variable needs to be Token.

Does this help?  It worked well for us.

[imanisha]

Yes,, you can use case statements while specifying joins and that way u can have dynamic joins depending on which join criteris fulfills the criteria at runtime.

Regards,
Mani