Enabling a cross join for only one table

[kerryn]

I am trying to solve an issue with cross joins.  I have a single record table that contains the date of when certain data was extracted.  By default our packages at set to DENY cross joins in the project governors.  If I have no relationship between this "current date" table and our time dimensions then if we try to bring the current date into an equation to build rolling time periods with the _add_months() function then we get cross join errors.

If I add a relationship between the "current date" and the time dimension then when this current date is called Cognos applies the join and filters the time dimension to only this one date which is not what I want.

Is there a way to get Cognos to cross join only one table in some sort of "relationship" definition why still enforcing the cross joins DENY for other tables?  Or is my only option to allow cross joins?

For a single filter in Report Studio ([Allocation date, calendar day,date] between _first_of_month ([Date of data extract]) and _add_months (_last_of_month ([Date of data extract]),18)), currently with a join, Cognos generates:

with
  "Allocation__Abs__date"
  as (select
        "BI_TDIM_TIME_ABS5"."TIME_ID" "TIME_ID"
      , "BI_TDIM_TIME_ABS5"."TIME_CD_YEAR_CATEGORY" "c14"
      , "BI_TDIM_TIME_ABS5"."TIME_DL_CMTH_NAME_LONG" "c39"
      , "BI_TDIM_TIME_ABS5"."TIME_NR_CMTH_PROG_YEAR" "c46"
      , "BI_TDIM_TIME_ABS5"."TIME_DT_DAY_START" "c55"
      from "LID"."BI_TDIM_TIME_ABS" "BI_TDIM_TIME_ABS5")
, "Data_Date"
  as (select
        "BI_TFCT_CURRENT_TIME"."TIME_ID_DAY" "TIME_ID_DAY"
      , "BI_TFCT_CURRENT_TIME"."TIME_DT_DAY" "Date_of_data_extract"
      from "LID"."BI_TFCT_CURRENT_TIME" "BI_TFCT_CURRENT_TIME")
select distinct
  "Allocation__Abs__date"."c14" "c1"
, "Allocation__Abs__date"."c39" "c2"
, "Allocation__Abs__date"."c46" "c3"
, "Data_Date"."Date_of_data_extract" "Date_of_data_extract"
from "Allocation__Abs__date"
left OUTER JOIN "Data_Date" on "Allocation__Abs__date"."TIME_ID"="Data_Date"."TIME_ID_DAY"
where "Allocation__Abs__date"."c55" between ("Data_Date"."Date_of_data_extract" - NUMTODSINTERVAL( EXTRACT( DAY FROM "Data_Date"."Date_of_data_extract" ), 'DAY' ) + INTERVAL '1' DAY) and CASE WHEN EXTRACT( DAY FROM ("Data_Date"."Date_of_data_extract" - NUMTODSINTERVAL( EXTRACT( DAY FROM "Data_Date"."Date_of_data_extract" ), 'DAY' ) + INTERVAL '1' DAY + INTERVAL '1' MONTH - INTERVAL '1' DAY) - NUMTODSINTERVAL( EXTRACT( DAY FROM ("Data_Date"."Date_of_data_extract" - NUMTODSINTERVAL( EXTRACT( DAY FROM "Data_Date"."Date_of_data_extract" ), 'DAY' ) + INTERVAL '1' DAY + INTERVAL '1' MONTH - INTERVAL '1' DAY) ), 'DAY' ) + INTERVAL '1' DAY + NUMTOYMINTERVAL( 18, 'MONTH' ) + INTERVAL '1' MONTH - INTERVAL '1' DAY ) < EXTRACT( DAY FROM ("Data_Date"."Date_of_data_extract" - NUMTODSINTERVAL( EXTRACT( DAY FROM "Data_Date"."Date_of_data_extract" ), 'DAY' ) + INTERVAL '1' DAY + INTERVAL '1' MONTH - INTERVAL '1' DAY) ) THEN ("Data_Date"."Date_of_data_extract" - NUMTODSINTERVAL( EXTRACT( DAY FROM "Data_Date"."Date_of_data_extract" ), 'DAY' ) + INTERVAL '1' DAY + INTERVAL '1' MONTH - INTERVAL '1' DAY) - NUMTODSINTERVAL( EXTRACT( DAY FROM ("Data_Date"."Date_of_data_extract" - NUMTODSINTERVAL( EXTRACT( DAY FROM "Data_Date"."Date_of_data_extract" ), 'DAY' ) + INTERVAL '1' DAY + INTERVAL '1' MONTH - INTERVAL '1' DAY) ), 'DAY' ) + INTERVAL '1' DAY + NUMTOYMINTERVAL( 18, 'MONTH' ) + INTERVAL '1' MONTH - INTERVAL '1' DAY ELSE ("Data_Date"."Date_of_data_extract" - NUMTODSINTERVAL( EXTRACT( DAY FROM "Data_Date"."Date_of_data_extract" ), 'DAY' ) + INTERVAL '1' DAY + INTERVAL '1' MONTH - INTERVAL '1' DAY) + NUMTOYMINTERVAL( 18, 'MONTH' ) END
order by "c1" asc nulls last, "c3" asc nulls last

I would like COgnos to generate instead:

with
  "Allocation__Abs__date"
  as (select
        "BI_TDIM_TIME_ABS5"."TIME_ID" "TIME_ID"
      , "BI_TDIM_TIME_ABS5"."TIME_CD_YEAR_CATEGORY" "c14"
      , "BI_TDIM_TIME_ABS5"."TIME_DL_CMTH_NAME_LONG" "c39"
      , "BI_TDIM_TIME_ABS5"."TIME_NR_CMTH_PROG_YEAR" "c46"
      , "BI_TDIM_TIME_ABS5"."TIME_DT_DAY_START" "c55"
      from "LID"."BI_TDIM_TIME_ABS" "BI_TDIM_TIME_ABS5")
, "Data_Date"
  as (select
        "BI_TFCT_CURRENT_TIME"."TIME_ID_DAY" "TIME_ID_DAY"
      , "BI_TFCT_CURRENT_TIME"."TIME_DT_DAY" "Date_of_data_extract"
      from "LID"."BI_TFCT_CURRENT_TIME" "BI_TFCT_CURRENT_TIME")
select distinct
  "Allocation__Abs__date"."c14" "c1"
, "Allocation__Abs__date"."c39" "c2"
, "Allocation__Abs__date"."c46" "c3"
, "Data_Date"."Date_of_data_extract" "Date_of_data_extract"
from "Allocation__Abs__date"
CROSS JOIN "Data_Date" on "Allocation__Abs__date"."TIME_ID"="Data_Date"."TIME_ID_DAY"
where "Allocation__Abs__date"."c55" between ("Data_Date"."Date_of_data_extract" - NUMTODSINTERVAL( EXTRACT( DAY FROM "Data_Date"."Date_of_data_extract" ), 'DAY' ) + INTERVAL '1' DAY) and CASE WHEN EXTRACT( DAY FROM ("Data_Date"."Date_of_data_extract" - NUMTODSINTERVAL( EXTRACT( DAY FROM "Data_Date"."Date_of_data_extract" ), 'DAY' ) + INTERVAL '1' DAY + INTERVAL '1' MONTH - INTERVAL '1' DAY) - NUMTODSINTERVAL( EXTRACT( DAY FROM ("Data_Date"."Date_of_data_extract" - NUMTODSINTERVAL( EXTRACT( DAY FROM "Data_Date"."Date_of_data_extract" ), 'DAY' ) + INTERVAL '1' DAY + INTERVAL '1' MONTH - INTERVAL '1' DAY) ), 'DAY' ) + INTERVAL '1' DAY + NUMTOYMINTERVAL( 18, 'MONTH' ) + INTERVAL '1' MONTH - INTERVAL '1' DAY ) < EXTRACT( DAY FROM ("Data_Date"."Date_of_data_extract" - NUMTODSINTERVAL( EXTRACT( DAY FROM "Data_Date"."Date_of_data_extract" ), 'DAY' ) + INTERVAL '1' DAY + INTERVAL '1' MONTH - INTERVAL '1' DAY) ) THEN ("Data_Date"."Date_of_data_extract" - NUMTODSINTERVAL( EXTRACT( DAY FROM "Data_Date"."Date_of_data_extract" ), 'DAY' ) + INTERVAL '1' DAY + INTERVAL '1' MONTH - INTERVAL '1' DAY) - NUMTODSINTERVAL( EXTRACT( DAY FROM ("Data_Date"."Date_of_data_extract" - NUMTODSINTERVAL( EXTRACT( DAY FROM "Data_Date"."Date_of_data_extract" ), 'DAY' ) + INTERVAL '1' DAY + INTERVAL '1' MONTH - INTERVAL '1' DAY) ), 'DAY' ) + INTERVAL '1' DAY + NUMTOYMINTERVAL( 18, 'MONTH' ) + INTERVAL '1' MONTH - INTERVAL '1' DAY ELSE ("Data_Date"."Date_of_data_extract" - NUMTODSINTERVAL( EXTRACT( DAY FROM "Data_Date"."Date_of_data_extract" ), 'DAY' ) + INTERVAL '1' DAY + INTERVAL '1' MONTH - INTERVAL '1' DAY) + NUMTOYMINTERVAL( 18, 'MONTH' ) END
order by "c1" asc nulls last, "c3" asc nulls last


Any ideas?

[blom0344]

You can emulate a cross-join by bringing in a fixed value (like 'x')  in both subjects and performing a regular join over this item. You are then using a dummy value. The resultset returned is the same as a cross-join..

[SSNCOG]

Hi,

I think we can force the RS query to allow crossjoin.click on query and try to change the cross join property and see if it works.

SSNCOG