asked
ON CLAUSE WITHIN INNER JOIN
Hi Everyone, I'm pretty new to SQL and need help as the SQL runs fine without the ON clause highlighted. But I'm trying to put an ON clause to avoid any cartesian product. But its comes back with error. I couldn't figure out myself and hope someone can guide me. Thanks! SELECT sum(distinct New_ACA_Claims_D.READMITS) over (partition by New_ACA_Claims_D.ACA_ID,New_ACA_Claims_D.Practioner_Group,New_ACA_Claims_D.Practioner) as Readmits_Practioner, sum(distinct New_ACA_Claims_D.READMITS) over (partition by New_ACA_Claims_D.ACA_ID,New_ACA_Claims_D.Practioner_Group) as Readmits_PG, sum(distinct New_ACA_Claims_D.READMITS) over () as Readmits_ACA, sum(distinct New_ACA_Claims_D.MEMBER_ADMITS) over (partition by New_ACA_Claims_D.ACA_ID,New_ACA_Claims_D.Practioner_Group,New_ACA_Claims_D.Practioner) as MM_Admits_Practioner, sum(distinct New_ACA_Claims_D.MEMBER_ADMITS) over (partition by New_ACA_Claims_D.ACA_ID,New_ACA_Claims_D.Practioner_Group) as MM_Admits_PG, sum(distinct New_ACA_Claims_D.MEMBER_ADMITS) over () as MM_Admits_ACA, sum(distinct New_ACA_Claims_D.MEMBER_MONTHS_RX) over (partition by New_ACA_Claims_D.ACA_ID,New_ACA_Claims_D.Practioner_Group) as MM_Rx_PG, sum(distinct New_ACA_Claims_D.MEMBER_MONTHS_RX) over () as MM_Rx_ACA, sum(distinct New_ACA_Claims_D.MEMBER_MONTHS_RX) over (partition by New_ACA_Claims_D.ACA_ID,New_ACA_Claims_D.Practioner_Group,New_ACA_Claims_D.Practioner) as MM_Rx_Practioner, sum(distinct New_ACA_Claims_D.MEMBER_MONTHS) over (partition by New_ACA_Claims_D.ACA_ID,New_ACA_Claims_D.Practioner_Group) as MM_PG, sum(distinct New_ACA_Claims_D.MEMBER_MONTHS) over () as MM_ACA, sum(distinct New_ACA_Claims_D.MEMBER_MONTHS) over (partition by New_ACA_Claims_D.ACA_ID,New_ACA_Claims_D.Practioner_Group,New_ACA_Claims_D.Practioner) as MM_Practioner, sum(distinct New_ACA_Claims_D.WEIGHTED_RISK) over () as Weighted_ACA, sum(distinct New_ACA_Claims_D.WEIGHTED_RISK) over (partition by New_ACA_Claims_D.ACA_ID,New_ACA_Claims_D.Practioner_Group) as Weighted_PG, sum(distinct New_ACA_Claims_D.WEIGHTED_RISK) over (partition by New_ACA_Claims_D.ACA_ID,New_ACA_Claims_D.Practioner_Group,New_ACA_Claims_D.Practioner) as Weighted_Practioner, New_ACA_Claims_D.FINANCIAL_IND, New_ACA_Claims_D.IA_TIME, New_ACA_Claims_D.ACA_ID, New_ACA_Claims_D.PRACTIONER_GROUP, New_ACA_Claims_D.PRACTIONER, COALESCE(New_ACA_Claims_D.SERVICING_PROVIDER,'Pharmacy') as Servicing_Provider, COALESCE(New_ACA_Claims_D.SPECIALTY,'Pharmacy') as Speciality, New_ACA_Claims_D.SERVICE_BUCKET, New_ACA_Claims_D.LVL2, COALESCE(New_ACA_Claims_D.ETG,'Pharmacy') as ETG, sum(New_ACA_Claims_D.ADMITS+New_ACA_Claims_D.SCRIPTS+New_ACA_Claims_D.VISITS) as Utiliziation, sum(New_ACA_Claims_D.MEMBER_COUNT) as Member_Count, sum(New_ACA_Claims_D.ETG_COUNT) as ETG_Count, sum(New_ACA_Claims_D.VISITS) as Visits, sum(New_ACA_Claims_D.ADMITS) as Admits, sum(New_ACA_Claims_D.SCRIPTS) as Scripts, sum(New_ACA_Claims_D.AMOUNT_EQV) as Amount_Eqv, New_ACA_Claims_D.WEIGHTED_RISK, New_ACA_Claims_D.MEMBER_MONTHS, New_ACA_Claims_D.MEMBER_MONTHS_RX, New_ACA_Claims_D.MEMBER_ADMITS, New_ACA_Claims_D.READMITS FROM ADMIN.EXTRACT_CONTROL INNER JOIN ( SELECT Member_Serv.Extract_Date as Extract_Date, Member_Serv.Extract_Desc as Extract_Desc, Member_Serv.IA_Time as IA_Time, Member_Serv.ACA_ID as ACA_ID, Member_Serv. Practioner_Group as Practioner_Group, Member_Serv. Practioner as Practioner, Member_Serv.Servicing_Provider as Servicing_Provider, Member_Serv.Specialty as Specialty, CASE Member_Serv.Service_Bucket WHEN '1' then 'ER' WHEN '2' then 'Hospital' WHEN '3' then 'Laboratory' WHEN '4' then 'Primary Care' WHEN '5' then 'Radiology' WHEN '6' then 'Specialty Care' WHEN '7' then 'Pharmacy' ELSE 'Other' END as Service_Bucket, Member_Serv.lvl2 as lvl2, Member_Serv.ETG as ETG, count(Member_Serv.Member) as Member_Count, count(distinct Member_Serv.Episode_ID) as ETG_Count, count(distinct Member_Serv.Date_of_Service) as Visits, CASE Member_Serv.Service_Bucket WHEN '2' then sum(con_Admits) END as Admits, CASE Member_Serv.Service_Bucket WHEN '7' then sum(Script_ct) END as Scripts, sum(Member_Serv.Amount_Eqv) as Amount_Eqv, Total_Members.Weighted_Risk as Weighted_Risk, Total_Members.Member_Months as Member_Months, Total_Members.Member_Months_Rx as Member_Months_Rx, Total_Members.Member_Admits as Member_Admits, Total_Members.Readmits as Readmits, Total_Members.Financial_Ind as Financial_Ind from (SELECT ADMIN.EXTRACT_CONTROL.EXTC_TS as Extract_Date, ADMIN.EXTRACT_CONTROL.EXTC_DC as Extract_Desc, ADMIN.SERVICES_MED.IA_TIME as IA_Time, ADMIN.VW_BO_MEM_INFO.ACT_IMP_ACA_ID as ACA_ID, ADMIN.VW_BO_MEM_INFO.ACT_IMP_PROV_AFFIL_DESC as Practioner_Group, ADMIN.VW_BO_MEM_INFO.ACT_IMP_PCP_NAME as Practioner, ADMIN.VW_BO_PROV_INFO.PROVIDER_NAME as Servicing_Provider, ADMIN.VW_BO_PROV_INFO.SP3 as Specialty, ADMIN.SERVICES_MED.PSC_CAT1_ID as Service_Bucket, ADMIN.SERVICES_MED.PSC_CAT2_ID as lvl2, ADMIN.VW_BO_EPISODES.ETG_IMPACT_DESC as ETG, ADMIN.SERVICES_MED.EPISODE_ID as Episode_ID, ADMIN.SERVICES_MED.MEMBER as Member, ADMIN.SERVICES_MED.DOS as Date_of_Service, sum(0) as Con_Admits, sum(0) as Script_ct, sum(ADMIN.SERVICES_MED.AMT_EQV) as Amount_Eqv FROM ADMIN.VW_BO_MEM_INFO INNER JOIN ADMIN.EXTRACT_CONTROL ON (ADMIN.EXTRACT_CONTROL.EXTC_TS=ADMIN.VW_BO_MEM_INFO.EXTC_TS) INNER JOIN ADMIN.SERVICES_MED ON (ADMIN.SERVICES_MED.MEMBER=ADMIN.VW_BO_MEM_INFO.MEMBER and ADMIN.SERVICES_MED.EXTC_TS=ADMIN.VW_BO_MEM_INFO.EXTC_TS and ADMIN.SERVICES_MED.IA_TIME=ADMIN.VW_BO_MEM_INFO.IA_TIME) LEFT OUTER JOIN ADMIN.VW_BO_EPISODES ON (ADMIN.SERVICES_MED.EXTC_TS=ADMIN.VW_BO_EPISODES.EXTC_TS and ADMIN.SERVICES_MED.EPISODE_ID=ADMIN.VW_BO_EPISODES.EPISODE_ID) LEFT OUTER JOIN ADMIN.VW_BO_PROV_INFO ON (ADMIN.SERVICES_MED.EXTC_TS=ADMIN.VW_BO_PROV_INFO.EXTC_TS and ADMIN.SERVICES_MED.PROVIDER_ID=ADMIN.VW_BO_PROV_INFO.PROVIDER_ID) GROUP BY ADMIN.EXTRACT_CONTROL.EXTC_TS, ADMIN.EXTRACT_CONTROL.EXTC_DC, ADMIN.SERVICES_MED.IA_TIME, ADMIN.VW_BO_MEM_INFO.ACT_IMP_ACA_ID, ADMIN.VW_BO_MEM_INFO.ACT_IMP_PROV_AFFIL_DESC, ADMIN.VW_BO_MEM_INFO.ACT_IMP_PCP_NAME, ADMIN.VW_BO_PROV_INFO.PROVIDER_NAME, ADMIN.VW_BO_PROV_INFO.SP3, ADMIN.SERVICES_MED.PSC_CAT1_ID, ADMIN.SERVICES_MED.PSC_CAT2_ID, ADMIN.VW_BO_EPISODES.ETG_IMPACT_DESC, ADMIN.SERVICES_MED.EPISODE_ID, ADMIN.SERVICES_MED.MEMBER, ADMIN.SERVICES_MED.DOS UNION ALL SELECT ADMIN.EXTRACT_CONTROL.EXTC_TS as Extract_Date, ADMIN.EXTRACT_CONTROL.EXTC_DC as Extract_Desc, ADMIN.SERVICES_RX.IA_TIME as IA_Time, ADMIN.VW_BO_MEM_INFO.ACT_IMP_ACA_ID as ACA_ID, ADMIN.VW_BO_MEM_INFO.ACT_IMP_PROV_AFFIL_DESC as Practioner_Group, ADMIN.VW_BO_MEM_INFO.ACT_IMP_PCP_NAME as Practioner, 'Pharmacy' as Servicing_Provider, 'Pharmacy' as Specialty, ADMIN.SERVICES_RX.PSC_CAT1_ID as Service_Bucket, 0 as lvl2, 'Pharmacy' as ETG, 0 as Episode_ID, ADMIN.SERVICES_RX.MEMBER as Member, '12/31/9999' as Date_of_Service, sum(0) as Con_Admits, sum(ADMIN.SERVICES_RX.SCRIPT) as Script_ct, sum(ADMIN.SERVICES_RX.AMT_EQV) as Amount_Eqv FROM ADMIN.VW_BO_MEM_INFO INNER JOIN ADMIN.EXTRACT_CONTROL ON (ADMIN.EXTRACT_CONTROL.EXTC_TS=ADMIN.VW_BO_MEM_INFO.EXTC_TS) INNER JOIN ADMIN.SERVICES_RX ON (ADMIN.SERVICES_RX.MEMBER=ADMIN.VW_BO_MEM_INFO.MEMBER and ADMIN.SERVICES_RX.EXTC_TS=ADMIN.VW_BO_MEM_INFO.EXTC_TS and ADMIN.SERVICES_RX.IA_TIME=ADMIN.VW_BO_MEM_INFO.IA_TIME) GROUP BY ADMIN.EXTRACT_CONTROL.EXTC_TS, ADMIN.EXTRACT_CONTROL.EXTC_DC, ADMIN.SERVICES_RX.IA_TIME, ADMIN.VW_BO_MEM_INFO.ACT_IMP_ACA_ID, ADMIN.VW_BO_MEM_INFO.ACT_IMP_PROV_AFFIL_DESC, ADMIN.VW_BO_MEM_INFO.ACT_IMP_PCP_NAME, ADMIN.SERVICES_RX.PSC_CAT1_ID, ADMIN.SERVICES_RX.MEMBER, ADMIN.SERVICES_RX.SCRIPT UNION ALL SELECT ADMIN.EXTRACT_CONTROL.EXTC_TS as Extract_Date, ADMIN.EXTRACT_CONTROL.EXTC_DC as Extract_Desc, ADMIN.CONFINEMENTS.IA_TIME as IA_Time, ADMIN.VW_BO_MEM_INFO.ACT_IMP_ACA_ID as ACA_ID, ADMIN.VW_BO_MEM_INFO.ACT_IMP_PROV_AFFIL_DESC as Practioner_Group, ADMIN.VW_BO_MEM_INFO.ACT_IMP_PCP_NAME as Practioner, ADMIN.VW_BO_PROV_INFO.PROVIDER_NAME as Servicing_Provider, ADMIN.VW_BO_PROV_INFO.SP3 as Specialty, ADMIN.CONFINEMENTS.PSC_CAT1_ID as Service_Bucket, ADMIN.CONFINEMENTS.PSC_CAT2_ID as lvl2, ADMIN.VW_BO_EPISODES.ETG_IMPACT_DESC as ETG, ADMIN.CONFINEMENTS.EPISODE_ID as Episode_ID, ADMIN.CONFINEMENTS.MEMBER as Member, ADMIN.CONFINEMENTS.BEG_DT as Date_of_Service, sum(ADMIN.CONFINEMENTS.ADMIT) as Con_Admits, sum(0) as Script_ct, sum( ADMIN.CONFINEMENTS.AMT_EQV ) as Amount_Eqv FROM ADMIN.VW_BO_MEM_INFO INNER JOIN ADMIN.EXTRACT_CONTROL ON (ADMIN.EXTRACT_CONTROL.EXTC_TS=ADMIN.VW_BO_MEM_INFO.EXTC_TS) INNER JOIN ADMIN.CONFINEMENTS ON (ADMIN.CONFINEMENTS.MEMBER=ADMIN.VW_BO_MEM_INFO.MEMBER and ADMIN.CONFINEMENTS.EXTC_TS=ADMIN.VW_BO_MEM_INFO.EXTC_TS and ADMIN.CONFINEMENTS.IA_TIME=ADMIN.VW_BO_MEM_INFO.IA_TIME) LEFT OUTER JOIN ADMIN.VW_BO_EPISODES ON (ADMIN.CONFINEMENTS.EXTC_TS=ADMIN.VW_BO_EPISODES.EXTC_TS and ADMIN.CONFINEMENTS.EPISODE_ID=ADMIN.VW_BO_EPISODES.EPISODE_ID) LEFT OUTER JOIN ADMIN.VW_BO_PROV_INFO ON (ADMIN.CONFINEMENTS.EXTC_TS=ADMIN.VW_BO_PROV_INFO.EXTC_TS and ADMIN.CONFINEMENTS.PROVIDER_ID=ADMIN.VW_BO_PROV_INFO.PROVIDER_ID) GROUP BY ADMIN.EXTRACT_CONTROL.EXTC_TS, ADMIN.EXTRACT_CONTROL.EXTC_DC, ADMIN.CONFINEMENTS.IA_TIME, ADMIN.VW_BO_MEM_INFO.ACT_IMP_ACA_ID, ADMIN.VW_BO_MEM_INFO.ACT_IMP_PROV_AFFIL_DESC, ADMIN.VW_BO_MEM_INFO.ACT_IMP_PCP_NAME, ADMIN.VW_BO_PROV_INFO.PROVIDER_NAME, ADMIN.VW_BO_PROV_INFO.SP3, ADMIN.CONFINEMENTS.PSC_CAT1_ID, ADMIN.CONFINEMENTS.PSC_CAT2_ID, ADMIN.VW_BO_EPISODES.ETG_IMPACT_DESC, ADMIN.CONFINEMENTS.EPISODE_ID, ADMIN.CONFINEMENTS.MEMBER, ADMIN.CONFINEMENTS.BEG_DT) Member_Serv INNER JOIN ( SELECT Member.Extract_Date, Member.Extract_Desc, Member.ACA_ID, Member.Practioner_Group, Member.Practioner, Member.IA_Time, Member.Financial_Ind, Member.Weighted_Risk, Member.Member_Months, Member.Member_Months_Rx, Member.Member_Admits, Member.Readmits FROM ( SELECT ADMIN.EXTRACT_CONTROL.EXTC_TS as Extract_Date, ADMIN.EXTRACT_CONTROL.EXTC_DC as Extract_Desc, ADMIN.VW_BO_MEM_INFO.ACT_IMP_ACA_ID as ACA_ID, ADMIN.VW_BO_MEM_INFO.ACT_IMP_PROV_AFFIL_DESC as Practioner_Group, ADMIN.VW_BO_MEM_INFO.ACT_IMP_PCP_NAME as Practioner, ADMIN.VW_BO_MEM_INFO.IA_TIME as IA_Time, Financial.Financial_Ind as Financial_Ind, sum(ADMIN.VW_BO_MEM_INFO.WRRISK) as Weighted_Risk, sum(ADMIN.VW_BO_MEM_INFO.TOT_MM) as Member_Months, sum(ADMIN.VW_BO_MEM_INFO.TOT_PHM_MM) as Member_Months_Rx, sum(ADMIN.VW_BO_MEM_INFO.ADMIT) as Member_Admits, sum(Readmit.Readmitted) as Readmits FROM ADMIN.VW_BO_MEM_INFO INNER JOIN ADMIN.EXTRACT_CONTROL ON (ADMIN.EXTRACT_CONTROL.EXTC_TS=ADMIN.VW_BO_MEM_INFO.EXTC_TS) LEFT JOIN (SELECT ADMIN.EXTRACT_CONTROL.EXTC_DC as Extract_Desc, ADMIN.VW_DAYS2_THRU_90_READMITS.IA_TIME as IA_Time, ADMIN.VW_DAYS2_THRU_90_READMITS.MEMBER as Member, count(distinct ADMIN.VW_DAYS2_THRU_90_READMITS.DAYS_FRM_DISCHRG) as Readmitted FROM ADMIN.VW_DAYS2_THRU_90_READMITS INNER JOIN ADMIN.EXTRACT_CONTROL ON (ADMIN.EXTRACT_CONTROL.EXTC_TS=ADMIN.VW_DAYS2_THRU_90_READMITS.EXTC_TS) WHERE (ADMIN.VW_DAYS2_THRU_90_READMITS.DAYS_FRM_DISCHRG <= 30) GROUP BY ADMIN.EXTRACT_CONTROL.EXTC_DC, ADMIN.VW_DAYS2_THRU_90_READMITS.IA_TIME, ADMIN.VW_DAYS2_THRU_90_READMITS.MEMBER ) Readmit ON (Readmit.Extract_Desc=ADMIN.EXTRACT_CONTROL.EXTC_DC and Readmit.IA_Time=ADMIN.VW_BO_MEM_INFO.IA_TIME and Readmit.Member=ADMIN.VW_BO_MEM_INFO.Member) INNER JOIN (SELECT ADMIN.EXTRACT_CONTROL.EXTC_DC as Extract_Desc, ADMIN.ACA_PROVIDER.FIN_INCL_IND as Financial_Ind, ADMIN.ACA_PROVIDER.PROV_AFFIL_DESC as Provider_Desc FROM ADMIN.ACA_PROVIDER INNER JOIN ADMIN.EXTRACT_CONTROL ON (ADMIN.EXTRACT_CONTROL.EXTC_TS=ADMIN.ACA_PROVIDER.EXTC_TS) GROUP BY ADMIN.EXTRACT_CONTROL.EXTC_DC, ADMIN.ACA_PROVIDER.FIN_INCL_IND, ADMIN.ACA_PROVIDER.PROV_AFFIL_DESC ) Financial ON (Financial.Extract_Desc=ADMIN.EXTRACT_CONTROL.EXTC_DC and Financial.Provider_Desc=ADMIN.VW_BO_MEM_INFO.ACT_IMP_PROV_AFFIL_DESC) GROUP BY ADMIN.EXTRACT_CONTROL.EXTC_TS, ADMIN.EXTRACT_CONTROL.EXTC_DC, ADMIN.VW_BO_MEM_INFO.ACT_IMP_ACA_ID, ADMIN.VW_BO_MEM_INFO.ACT_IMP_PROV_AFFIL_DESC, ADMIN.VW_BO_MEM_INFO.ACT_IMP_PCP_NAME, ADMIN.VW_BO_MEM_INFO.IA_TIME, Financial.Financial_Ind ) Member ***ON (Member.Extract_Date=Member_Serv.Extract_Date and Member_Serv.Extract_Desc=Member.Extract_Desc and Member_Serv.IA_Time=Member.IA_Time and Member_Serv.ACA_ID=Member.ACA_ID and Member_Serv.Practioner_Group=Member.Practioner_Group and Member_Serv.Practioner=Member.Practioner)*** ) Total_Members ON (Total_Members.Extract_Date=Member_Serv.Extract_Date and Member_Serv.Extract_Desc=Total_Members.Extract_Desc and Member_Serv.IA_Time=Total_Members.IA_Time and Member_Serv.ACA_ID=Total_Members.ACA_ID and Member_Serv.Practioner_Group=Total_Members.Practioner_Group and Member_Serv.Practioner=Total_Members.Practioner) GROUP BY Member_Serv.Extract_Date, Member_Serv.Extract_Desc, Member_Serv.IA_Time, Member_Serv.ACA_ID, Member_Serv.Practioner_Group, Member_Serv.Practioner, Member_Serv.Servicing_Provider, Member_Serv.Specialty, Member_Serv.Service_Bucket, Member_Serv.lvl2, Member_Serv.ETG, Total_Members.Weighted_Risk, Total_Members.Member_Months, Total_Members.Member_Months_Rx, Total_Members.Member_Admits, Total_Members.Readmits, Total_Members.Financial_Ind ) New_ACA_Claims_D ON (ADMIN.EXTRACT_CONTROL.EXTC_TS=New_ACA_Claims_D.EXTRACT_DATE and ADMIN.EXTRACT_CONTROL.EXTC_DC=New_ACA_Claims_D.EXTRACT_DESC) WHERE ( ADMIN.EXTRACT_CONTROL.EXTC_DC = 'Production Quarterly Run - 125K Member Catastrophic Limit - 4Q12' and New_ACA_Claims_D.IA_TIME = 1 and New_ACA_Claims_D.ACA_ID = 'SPIR' ) GROUP BY New_ACA_Claims_D.FINANCIAL_IND, New_ACA_Claims_D.IA_TIME, New_ACA_Claims_D.ACA_ID, New_ACA_Claims_D.PRACTIONER_GROUP, New_ACA_Claims_D.PRACTIONER, COALESCE(New_ACA_Claims_D.SERVICING_PROVIDER,'Pharmacy'), COALESCE(New_ACA_Claims_D.SPECIALTY,'Pharmacy'), New_ACA_Claims_D.SERVICE_BUCKET, New_ACA_Claims_D.LVL2, COALESCE(New_ACA_Claims_D.ETG,'Pharmacy'), New_ACA_Claims_D.WEIGHTED_RISK, New_ACA_Claims_D.MEMBER_MONTHS, New_ACA_Claims_D.MEMBER_MONTHS_RX, New_ACA_Claims_D.MEMBER_ADMITS, New_ACA_Claims_D.READMITS
I'm not completely sure where the issue lies, because that's WAY too much code for me to parse without getting paid, but you've got a missing parenthesis somewhere. I added one right before Member and the syntax error that was being generated at the ON statement was cleared. Of course, now, I get a syntax error down at New_ACA_Claims_D. You need to figure out where you went wrong with that parenthesis. In general, this code looks to be following a serious anti-pattern with all the nested derived tables joining on nested derived tables, multiple aggregates and then aggregates on the aggregates... In general, you're digging something of a big, deep hole here. I'd suggest trying to step back, reevaluate what you're trying to actually achieve with the query and see if there aren't better mechanisms for making that happen.
13 People are following this question.
