Splitted queries in RS (stitch queries)

[fabienM]

Hello

Here is my problem :
We are changing our DWH, we are migrating from Netezza to Snowflake.
All our reports are designed like this :

- Query - Date filter (for example get CURRENT DAY in the DWH context) :

https://ibb.co/1vK2jk0

- Query - Facts (a measure with a filter on the date with the result of the query "Query - Date filter") :

https://ibb.co/C5DwdzD

A)   With Netezza, Cognos generates 1 query with a sub-query correctly joined with the filter :
select "D_Date"."DayId" "Date1" , sum("F_Facts"."QTY") "Quantities"
from
"ETL_COGNOS"."D_Date" "D_Date",
"ETL_COGNOS"."F_Facts" "F_Facts",
(
       select distinct "D_Date"."DayId" "Date1"
        from "ETL_COGNOS"."D_Date" "D_Date"
       where "D_Date"."CURRENTDAY" = 0 and "D_Date"."CURRENT_R_YEAR" between -4 and 1
) "Query1"
where
"D_Date"."CURRENT_R_YEAR" between -4 and 1
and "D_Date"."DayId" = "Query1"."Date1"
and "F_Facts"."Transaction_Date" = "D_Date"."Day_SID"
group by "D_Date"."DayId"


B)   With Snowflake, Cognos generates 2 queries (stitch queries?) not joined...
select "DWH_TEST_COGNOS"."PUBLIC"."D_DATE"."DAYID" "Date1" , "DWH_TEST_COGNOS"."PUBLIC"."D_DATE"."CURRENTDAY" "Current_Day"
from
"DWH_TEST_COGNOS"."PUBLIC"."D_DATE"
where
"CURRENT_R_YEAR" between -4 and 1 and "DWH_TEST_COGNOS"."PUBLIC"."D_DATE"."CURRENTDAY" = 0

select "D_Date"."DAYID", "F_FACTS"."QTY"
from
"DWH_TEST_COGNOS"."PUBLIC"."D_DATE" "D_Date",
"DWH_TEST_COGNOS"."PUBLIC"."F_FACTS" "F_Facts"
where
"F_Facts"."TRANSACTION_DATE" = "D_Date"."DAY_SID"
and "D_Date"."CURRENT_R_YEAR" >= -4 and "D_Date"."CURRENT_R_YEAR" <= 1
order by "D_Date"."DAYID" asc


What is the problem?
How to generate a subquery like with Netezza?

Thank you!
Fabien