COGNOS 8 - Comples JOINs in Framework Mgr.

[sln007]

How complex, beyond simply equi-joining the primary keys of 2 tables, can you comfortably get?

We'd like to ad a sub-query into the look-up tables' expression in order to get "effective-dated" information.


Steve N.
State of Ohio, MIS

 

[PaulABC]

This is the most complex join expression I've used between two tables.

HR.PERIOD_CODE BETWEEN
MAP.FROM_PERIOD_CODE AND MAP.TO_PERIOD_CODE
AND HR.FIGURE_TYPE LIKE MAP.FIGURE_TYPE

The query generates OK and it runs without problems.

 

[sln007]

Thanks.

Turns out I ended up getting pretty involved. I am even using a #prompt macro, so far it seems to be right. Here's the text of the FM Filter:

( ( #prompt('Use Effective Dating? (enter Y or N)', 'character', 'Y')# in ('Y', 'y') ) AND ([test1].[BATS Benefit Billing Reporting Table].[Effective Date] <= to_date ( {sysdate} ) ) AND ( [test1].[BATS Benefit Billing Reporting Table].[Employee ID] = [test1].[Ben Billing Max Eff Date].[Employee ID] and [test1].[BATS Benefit Billing Reporting Table].[Employee Record Number] = [test1].[Ben Billing Max Eff Date].[Employee Record Number] and [test1].[BATS Benefit Billing Reporting Table].[Plan Type] = [test1].[Ben Billing Max Eff Date].[Plan Type] and [test1].[BATS Benefit Billing Reporting Table].[COBRA Event ID] = [test1].[Ben Billing Max Eff Date].[COBRA Event ID] and [test1].[BATS Benefit Billing Reporting Table].[SETID] = [test1].[Ben Billing Max Eff Date].[SETID] and [test1].[BATS Benefit Billing Reporting Table].[Department ID] = [test1].[Ben Billing Max Eff Date].[Department ID] and [test1].[BATS Benefit Billing Reporting Table].[Effective Date] = [test1].[Ben Billing Max Eff Date].[MAX Eff Date] ) ) OR ( #prompt('Use Effective Dating? (enter Y or N)', 'character', 'Y')# not in ('Y', 'y') )

I've learned alot with this specific issue.


Steve N.
State of Ohio, MIS