select OP_ID, OAM_ID, OAM_START_TIME, OAM_START_TIME_YEAR_DAY0, OAM_START_TIME_YEAR_DAY1, DAYOFYEAR(OAM_START_TIME_YEAR_DAY) as OAM_START_TIME_YEAR_DAY2, OAM_START_TIME_YEAR_DAY3, OAM_START_TIME_YEAR_DAY4 FROM
(
select 
EBI_ES_OPB.OP_ID OP_ID,
OAM_ID,
OAM_START_TIME as OAM_START_TIME, -- COALSESCE NOT CALLED, YIELDS NULL AS EXPECTED
DAYOFYEAR(COALESCE(OAM_START_TIME,STR_TO_DATE('1970-01-01', '%Y-%m-%d'))) AS OAM_START_TIME_YEAR_DAY0, -- DOES NOT WORK, BUG!!!
DAYOFYEAR(ifnull(OAM_START_TIME,STR_TO_DATE('1970-01-01', '%Y-%m-%d'))) AS OAM_START_TIME_YEAR_DAY1, -- DOES NOT WORK, BUG!!!
COALESCE(OAM_START_TIME,STR_TO_DATE('1970-01-01', '%Y-%m-%d')) AS OAM_START_TIME_YEAR_DAY, -- WORKS, AS DAYOFYEAR IS CALLED _OUTSIDE_ ==> OAM_START_TIME_YEAR_DAY2
DAYOFYEAR(if(OAM_START_TIME is null,STR_TO_DATE('1970-01-01', '%Y-%m-%d'),OAM_START_TIME)) AS OAM_START_TIME_YEAR_DAY3, -- WORKS
case when OAM_START_TIME is null Then DAYOFYEAR(STR_TO_DATE('1970-01-01', '%Y-%m-%d')) ELSE DAYOFYEAR(OAM_START_TIME) END OAM_START_TIME_YEAR_DAY4 -- WORKS
FROM
EBI_ES_OPB EBI_ES_OPB
		LEFT JOIN EBI_ES_OAM EBI_ES_OAM ON EBI_ES_OPB.OP_ID = EBI_ES_OAM.OP_ID
		WHERE 
         EBI_ES_OPB.OP_ID = 1878188
) a
;
