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
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
Quote from: patrickthatcher on 09 Sep 2015 02:59:07 PM
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
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.
Quote from: MFGF on 09 Sep 2015 04:04:23 PM
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