How would I create this LEFT OUTER JOIN?

[patrickthatcher]

Can someone suggest how I can setup a left outer join in framework manager to accomplish the query below?  I have all the inner joins working correctly, but I cannot figure out this part out.
tia
Patrick

Code Select Expand


SELECT
...,
COUNT(a13.greg_date) AS days_no_holidays_or_weekends
FROM rrpmvp.TRAN_ITEM_SUMMARY_V a11
JOIN rrpmvp.ITEM_SKU_V a12 ON (a12.ITEM_SKU_ID =((a11.fill_store_div) * 10000000000000 + a11.ITEM_ID))
JOIN ewpmvp.DATE_DIM_V a14 ON a14.greg_date = a11.quote_date_first
LEFT JOIN ewpmvp.DATE_DIM_V a13 ON a13.greg_date BETWEEN a11.BOOK_DATE_FIRST AND a11.FILL_DATE AND a13.day_name NOT IN ('Sunday','Saturday')   /* This outer join*/
WHERE a11.FILL_METH IN ('DROPSHIP' )
AND a14.AMC_PERIOD_KEY = 201506
AND a11.quote_date_first <= ( DATE - 1 )
AND a11.fill_date<>'9999-12-31'
AND dept_id=598 AND appl_id='MCOM'
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13


[MFGF]

Can someone suggest how I can setup a left outer join in framework manager to accomplish the query below?  I have all the inner joins working correctly, but I cannot figure out this part out.
tia
Patrick

Code Select Expand


SELECT
...,
COUNT(a13.greg_date) AS days_no_holidays_or_weekends
FROM rrpmvp.TRAN_ITEM_SUMMARY_V a11
JOIN rrpmvp.ITEM_SKU_V a12 ON (a12.ITEM_SKU_ID =((a11.fill_store_div) * 10000000000000 + a11.ITEM_ID))
JOIN ewpmvp.DATE_DIM_V a14 ON a14.greg_date = a11.quote_date_first
LEFT JOIN ewpmvp.DATE_DIM_V a13 ON a13.greg_date BETWEEN a11.BOOK_DATE_FIRST AND a11.FILL_DATE AND a13.day_name NOT IN ('Sunday','Saturday')   /* This outer join*/
WHERE a11.FILL_METH IN ('DROPSHIP' )
AND a14.AMC_PERIOD_KEY = 201506
AND a11.quote_date_first <= ( DATE - 1 )
AND a11.fill_date<>'9999-12-31'
AND dept_id=598 AND appl_id='MCOM'
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13


Hi,

There is an ellipsis at the bottom of the relationship dialog which allows you to specify a relationship as an expression. Once you have done this, then all you need is 0..n at the relevant end of the cardinality definition.

MF.

[patrickthatcher]

Hi,

There is an ellipsis at the bottom of the relationship dialog which allows you to specify a relationship as an expression. Once you have done this, then all you need is 0..n at the relevant end of the cardinality definition.

MF.

Perfect!  Worked like a charm.
Thank you