SQLTeam.com | Weblogs | Forums

Nested Query?

sql2014

#1

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


#3

What does that mean?

You want to re-write this because?

If possible, stick to 'Inner Join' and 'Left Outer Join'. It won't change the outcome but good to keep consistent and follow common standards. Unless 'Join' and 'Left Join' is your standard.