Scenario:
In business model , Below are the relationships defined:
Table A is left outer joined with Table B
Table A is left outer joined with Table C
Table A is left outer joined with Table D
Table A is left outer joined with Table E
Adding screenshot for reference:
Based on this, I am looking for the query output as below:
Expected Output Query:
SELECT DISTINCT
--
--
--
--
FROM TableA
LEFT OUTER JOIN TableB ON (TableA.PrimaryKey = TableB.ForeignKey)
LEFT OUTER JOIN TableC ON (TableA.PrimaryKey = TableC.ForeignKey)
LEFT OUTER JOIN TableD ON (TableA.PrimaryKey = TableD.ForeignKey)
LEFT OUTER JOIN TableE ON (TableA.PrimaryKey = TableE.ForeignKey)
Actual Output:
SELECT DISTINCT
"BT_CHG_INFRASTRUCTURE_CHANGE"."Infrastructure Change ID" AS "COL0"
,"BT_CHG_WORKLOG"."Work Log Type" AS "COL1"
,"BT_TMS_TASK"."Task ID" AS "COL2"
,"BT_CHG_IMPACTED_AREAS"."Department" AS "COL3"
,"BT_CHG_ASSOCIATIONS"."Association Type01" AS "COL4"
FROM "AR System Schema"."TMS:Task" "BT_TMS_TASK" RIGHT OUTER JOIN
(
"AR System Schema"."CHG:Impacted Areas" "BT_CHG_IMPACTED_AREAS" RIGHT OUTER JOIN
(
"AR System Schema"."CHG:Associations" "BT_CHG_ASSOCIATIONS" RIGHT OUTER JOIN
(
"AR System Schema"."CHG:Infrastructure Change" "BT_CHG_INFRASTRUCTURE_CHANGE" LEFT OUTER JOIN "AR System Schema"."CHG:WorkLog" "BT_CHG_WORKLOG"
ON ( "BT_CHG_INFRASTRUCTURE_CHANGE"."Infrastructure Change ID" = "BT_CHG_WORKLOG"."Infrastructure Change ID" )
)
ON ( "BT_CHG_INFRASTRUCTURE_CHANGE"."Infrastructure Change ID" = "BT_CHG_ASSOCIATIONS"."Request ID02" )
)
ON ( "BT_CHG_INFRASTRUCTURE_CHANGE"."Infrastructure Change ID" = "BT_CHG_IMPACTED_AREAS"."Infrastructure Change ID" )
)
ON ( "BT_CHG_INFRASTRUCTURE_CHANGE"."Infrastructure Change ID" = "BT_TMS_TASK"."RootRequestID" )
I am using Pentaho Metadata Editor 9.3 version. How can we fix this and get the desired output ? Please share your thoughts as this is blocking the functionality we are trying to achieve.