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..
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.
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.
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?
I was able to find a solution by using prompt macro and parameter maps together. Thanks for your help.