I am dropping a few records from the CRF table. That is because they are not in there. They are in the WOF table. I need to run the WOF query first and then left join to the CRF so all records show. I am not sure how to do that. I have used sub queries in a where statement before and I don't believe this situation would apply. Does anyone have a good way of re-writing this code?
SELECT
CRF.DATE
,WOF.VALUE_ID
,WOF.NBR
,WOF.KEY
,WOF.DT_KEY
,PM.BEFORE
,COUNT(WOF.PLAYER) AS GAME
,LISTAGG(CASE
WHEN WOF.SERVICE = ''T'' THEN ''P''
WHEN WOF.SERVICE = ''C'' THEN ''V''
ELSE ''D''
END,''+'') WITHIN GROUP (ORDER BY WOF.SERVICE)AS WORK
,PM.AFTER
,DT.TYPE
,CASE WHEN CRF.TENURE = 1 THEN ''Players <= 3 months''
WHEN CRF.TENURE = 2 THEN ''Players 4-6 months''
WHEN CRF.TENURE = 3 THEN ''Players 7-12 months''
WHEN CRF.TENURE = 4 THEN ''Players 13-24 months''
WHEN CRF.TENURE = 5 THEN ''Players 25-36 months''
WHEN CRF.TENURE = 6 THEN ''Players 3 + years''
ELSE NULL
END AS PLAYER_TENURE
FROM WOF
JOIN TD
ON WOF.DT_KEY =TD.CALENDAR_KEY
JOIN SCD
ON WOF.PLAYER_KEY = SCD.PLAYER_KEY
JOIN SCH
ON SCD.HIER_KEY = SCH.HIER_KEY
JOIN DT
ON WOF.DWELLING = DT.DWELLING
LEFT JOIN CRF
ON WOF.KEY = CRF.KEY
AND WOF.VALUE_ID = CRF.VALUE_ID
AND WOF.NBR = CRF.NBR
AND TRUNC(LAST_DAY(WOF.TIME)) = CRF.DATE
JOIN PM
ON CRF.KEY = PM.KEY
WHERE WOF.TYPE_KEY = ''8''
AND WOF.GAMER_KEY =''1''
AND WOF.RANDOM_KEY IN (''25'',''594'',''7071'')
AND WOF.OUTLET = ''5''
AND WOF.BILL IN(''1'',''2'',''4'')
AND SCH.CHANNEL_NM=''HOLLER''
AND WOF.CD = ''C''
AND WOF.STATUS_CD = ''CP''
AND TD.YEAR_MONTH_NBR = ''201609''
GROUP BY
CRF.DATE
,WOF.VALUE_ID
,WOF.DT_KEY
,WOF.NBR
,WOF.KEY
,DT.TYPE
,CRF.TENURE
,PM.BEFORE
,PM.AFTER