Conditional Join in FM

[Redi]

Hello All

I have three table 1,2 and 3.
table 1 has col-a and col-b(date)
table 2 has col-a
table 3 has col-a and col-c(date) and col-d(date)

table 1 and table 2 are joined on col-a
table 2 and table 3 are joined on col-a

i have a requirement to get data from table 2 and table 3 cognos forms a sql with join on col-a that is good for most of the cases.
but i also have a requirement to get data from table 1 and table 3 cognos generated the join on col-a and i need an additional condition to be validated for this data i.e col-b (from table1) between col-c and col-d (from table3)
this is an ad hoc model so the solution has to be in the FM.
aliasing is not the best option for me at this time because i have many tables in fm where i need to use the same logic.

anyone has suggestions..

[tjohnson3050]

You can use a prompt macro in the join condition between table 2 and 3 in combination with and/or logic.  Click the button in the lower right of the join expression to go to the join expression editor.  Try starting with a new query subject based on table 1 and table 2 (call this qs1).

For example:

qs1.col-a = table3.col-a
and
((#prompt('ExtraJoin','token','N')# = 'N')
OR
(#prompt('ExtraJoin','token','Y')# = 'Y' and qs1.col-b between table3.col-c and table3.col-d))

You can then use the ExtraJoin parameter to determine if this extra join should be invoked.

[Redi]

Thanks for your reply. my model is ad hoc model the table 1,2,3 have to be in seperate QS i cannot combine them and also there are many other dimension tables joined to table1 and table2. I am looking for a solution that i could implement on multiple QS in the model. I will try the prompt macro option and see if it works for me. Any other comments on this are appreciated.

[bdbits]

Perhaps its me, but I am a bit unclear on the roles for the tables - dim vs fact - and cardinality between them. It sounds like table1 and table2 are facts - is table3 a dimension? Are the relationships all one-to-many? Are your table relationships in the same layer as your query subjects, or a separate database layer?

[Redi]

I was able to find a solution by using prompt macro and parameter maps together. Thanks for your help.