SELECT fct.task_key, fct.resource_key, fct.investment_key, fct.ACTUAL_HOURS, fct.ACWP_COST, time_period_key, fiscl_p.year_name, fiscl_p.period_nameFROM DWH_INV_ASSIGN_PERIOD_FACTS fct INNER JOIN DWH_CMN_PERIOD_F_V FISCL_P ON fct.PERIOD_KEY = FISCL_P.PERIOD_KEY INNER JOIN dwh_tme_period TME ON TME.start_date = fiscl_p.period_start_Date AND TME.finish_date = fiscl_p.period_end_date AND FISCL_P.LANGUAGE_CODE = 'en'(Structure: Type, Object, Attribute, Join, Object, Attribute)
SELECT q.*FROM ( SELECT dwh_inv_investment.investment_key inv____investment_key, dwh_tme_sheet.timesheet_key timesheet____timesheet_key, dwh_inv_task.task_key task____task_key, dwh_tme_entry.timeentry_key timeentry____timeentry_key, dwh_res_resource.resource_key resource____resource_key, dwh_inv_assignment.assignment_key assignment____assignment_key, dwh_tme_period.time_period_key timeperiod____time_period_key, sql00000009.actual_hours odf_sql___sql00000009____actual_hours, sql00000009.investment_key odf_sql___sql00000009____investment_key, sql00000009.period_name odf_sql___sql00000009____period_name, row_number() over( ORDER BY 1 ) AS row_num, 5000000 + row_number() over( ORDER BY 1 ) AS odf_pk, COUNT(1) OVER() AS rows_count FROM ( SELECT fct.task_key, fct.resource_key, fct.investment_key, fct.ACTUAL_HOURS, fct.ACWP_COST, time_period_key, fiscl_p.year_name, fiscl_p.period_name FROM DWH_INV_ASSIGN_PERIOD_FACTS fct INNER JOIN DWH_CMN_PERIOD_F_V FISCL_P ON fct.PERIOD_KEY = FISCL_P.PERIOD_KEY INNER JOIN dwh_tme_period TME ON TME.start_date = fiscl_p.period_start_Date AND TME.finish_date = fiscl_p.period_end_date AND FISCL_P.LANGUAGE_CODE = 'en' ) sql00000009 RIGHT OUTER JOIN dwh_inv_investment dwh_inv_investment ON sql00000009.investment_key = dwh_inv_investment.investment_key RIGHT OUTER JOIN dwh_inv_task dwh_inv_task ON sql00000009.task_key = dwh_inv_task.task_key RIGHT OUTER JOIN dwh_res_resource dwh_res_resource ON sql00000009.resource_key = dwh_res_resource.resource_key RIGHT OUTER JOIN dwh_tme_period dwh_tme_period ON sql00000009.time_period_key = dwh_tme_period.time_period_key JOIN dwh_inv_assignment dwh_inv_assignment ON dwh_inv_task.task_key = dwh_inv_assignment.task_key JOIN dwh_tme_entry dwh_tme_entry ON dwh_tme_entry.assignment_key = dwh_inv_assignment.assignment_key JOIN dwh_tme_sheet dwh_tme_sheet ON dwh_tme_sheet.timesheet_key = dwh_tme_entry.timesheet_key WHERE 1 = 1 AND ( ( dwh_inv_investment.sub_type IN ('project', 'idea', 'cit') ) OR (dwh_inv_investment.investment_key IS NULL) ) ORDER BY row_num OFFSET 0 ROWS FETCH next 100 ROWS ONLY ) qORDER BY row_numClarity 16.4.1, 16.4.2
This is caused by DE196241
The behavior reported is working as designed.
When building a Data Provider query, Clarity uses a spanning-tree algorithm that starts from the primary object and visits each table once. If a join leads to a table that has already been visited, that join is skipped to prevent invalid or ambiguous SQL.
In Clarity 16.4.3 aliases have been introduced for Joining to Same Object in Data Providers.
Alias Objects will be the same as regular Objects and can be used in Joins, Filters, Pre-Filters, and Preview.