Re: Flaky MySQL?
Hi Roland,
I tried it out. I figured you could not use any of the conditions in the outer joins in the where clause, so left them there. Trouble is that I get a different number of rows for the different methods, cannot see any logical difference int he 2 approaches, but probably am not seeing something.
SELECT * FROM corax_dividends cd INNER JOIN EQUITIES_XREF eq ON cd.RIC = eq.RIC AND
(cd.Div_Feature NOT IN (10, 30, 33) OR (cd.Div_Feature IN (10, 30, 33) AND NOT EXISTS
(SELECT * FROM corax_dividends cd2 WHERE cd.RIC = cd2.RIC AND cd.Div_PILC = cd2.Div_PILC AND cd.Div_Pay_Date = cd2.Div_Pay_Date
AND cd.Ex_Dividend_Date = cd2.Ex_Dividend_Date AND cd2.Div_Feature IN (11, 31, 34))))
INNER JOIN cd_mt564_map mt ON cd.Div_Feature = mt.feature AND cd.Div_Type_Marker = mt.type_marker
INNER JOIN corax_event ev ON ev.RIC = cd.RIC AND ev.Event_Type = 1 AND ev.Div_Market_Lvl_ID = cd.Div_Market_Lvl_ID
LEFT OUTER JOIN corax_capital_change cc ON cc.RIC = cd.RIC AND cd.Div_Type_Marker = 30 AND DATEDIFF(cd.ENT_TIME, cc.ENT_TIME) <2 AND cc.Event_Type = 80 AND cd.Div_Currency <> 'PCT'
LEFT OUTER JOIN corax_capital_change cc2 ON cc2.RIC = cd.RIC AND cd.Div_Type_Marker = 31 AND DATEDIFF(cd.ENT_TIME, cc2.ENT_TIME) <2 AND cc2.Event_Type = 82 AND cd.Div_Currency <> 'PCT'
INNER JOIN customer_watch_list cw ON ev.SECURITY_ID = cw.SecurityId
INNER JOIN customer_portfolio cp ON cw.Portfolio_Id = cp.Portfolio_Id AND ev.Event_Time > cp.Last_Run
LEFT OUTER JOIN corax_event ev2 ON ev.RIC = ev2.RIC AND ev.Div_Market_Lvl_ID = ev2.Div_Market_Lvl_Id AND ev2.Event_Id <> ev.Event_Id AND
ev2.Event_Id = (SELECT MAX(ev3.Event_Id) FROM corax_event ev3 WHERE ev2.RIC = ev3.RIC AND ev2.Div_Market_Lvl_Id = ev3.Div_Market_Lvl_Id)
109 rows from the original one, which by the way is the common way I see it done by colleagues who use MSSQL.
SELECT * FROM corax_dividends cd INNER JOIN EQUITIES_XREF eq ON cd.RIC = eq.RIC
INNER JOIN cd_mt564_map mt ON cd.Div_Feature = mt.feature AND cd.Div_Type_Marker = mt.type_marker
INNER JOIN corax_event ev ON ev.RIC = cd.RIC AND ev.Div_Market_Lvl_ID = cd.Div_Market_Lvl_ID
LEFT OUTER JOIN corax_capital_change cc ON cc.RIC = cd.RIC AND cd.Div_Type_Marker = 30 AND DATEDIFF(cd.ENT_TIME, cc.ENT_TIME) <2 AND cc.Event_Type = 80 AND cd.Div_Currency <> 'PCT'
LEFT OUTER JOIN corax_capital_change cc2 ON cc2.RIC = cd.RIC AND cd.Div_Type_Marker = 31 AND DATEDIFF(cd.ENT_TIME, cc2.ENT_TIME) <2 AND cc2.Event_Type = 82 AND cd.Div_Currency <> 'PCT'
INNER JOIN customer_watch_list cw ON ev.SECURITY_ID = cw.SecurityId
INNER JOIN customer_portfolio cp ON cw.Portfolio_Id = cp.Portfolio_Id
LEFT OUTER JOIN corax_event ev2 ON ev.RIC = ev2.RIC AND ev.Div_Market_Lvl_ID = ev2.Div_Market_Lvl_Id
WHERE ev2.Event_Id =
(SELECT MAX(ev3.Event_Id) FROM corax_event ev3 WHERE ev2.RIC = ev3.RIC AND ev2.Div_Market_Lvl_Id = ev3.Div_Market_Lvl_Id)
AND ev2.Event_Id <> ev.Event_Id AND ev.Event_Type = 1 AND (
cd.Div_Feature NOT IN (10, 30, 33)
OR (cd.Div_Feature IN (10, 30, 33)
AND NOT EXISTS (SELECT *
FROM corax_dividends cd2
WHERE cd.RIC = cd2.RIC
AND cd.Div_PILC = cd2.Div_PILC
AND cd.Div_Pay_Date = cd2.Div_Pay_Date
AND cd.Ex_Dividend_Date = cd2.Ex_Dividend_Date
AND cd2.Div_Feature IN (11, 31, 34)))) AND ev.Event_Time > cp.Last_Run
43 rows from this new approach moving the non JOIN conditions into the where clause.
David