-1

I'm getting the infamous "Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY" error, on an EXISTS(select...). I've tried wrapping the patient_no in a MAX function, adding a GROUP BY in the select, to no avail. See below for SecondaryPayorClass. Note there is also a GROUP BY in the outer query. Anyone have any suggestions?

select
   case when fidr.priority='1' then
    CASE WHEN (en.PAYOR_CLASS = 'R'AND en.PAYOR_NO = '') THEN 'M' 
         WHEN (en.PAYOR_CLASS = 'R'AND fidr.INSURANCE_NO = '999999999A') THEN 'PM'
         WHEN (en.PAYOR_CLASS = 'R'AND en.PAYOR_NO = '15') THEN 'HC' 
         WHEN (en.PAYOR_CLASS = 'S') THEN 'MC'
         WHEN (en.PAYOR_CLASS = '5') THEN 'P'
         ELSE ''
    END
    when fidr.PRIORITY is null and en.PAYOR_CLASS = '6' then 'SP'
   end as PrimaryPayorClass,
   case when fidr.priority='2' then
    CASE WHEN (en.PAYOR_CLASS = 'R'AND en.PAYOR_NO = '') THEN 'M' 
         WHEN (en.PAYOR_CLASS = 'R'AND fidr.INSURANCE_NO = '999999999A') THEN 'PM'
         WHEN (en.PAYOR_CLASS = 'R'AND en.PAYOR_NO = '15') THEN 'HC' 
         WHEN (en.PAYOR_CLASS = 'S') THEN 'MC'
         WHEN (en.PAYOR_CLASS = '5') THEN 'P'
         ELSE ''
    END 
    WHEN exists(select f1.patient_no from fidfile f1        --Is SP the Secondary Class
                where (f1.patient_no=fidr.patient_no and f1.payor_class in ('R','S','5') 
                and ((f1.effective_end_date >= enh.encounter_date) OR (f1.effective_end_date IS NULL)))
                group by f1.patient_no, f1.payor_class)
         and (en.payor_class = '6') THEN 'SP'
end as SecondaryPayorClass,
SUM(isnull(A.CHARGE_AMOUNT,0)) AS ChargeAmount,
case
 when sum(isnull(a.adjustment_amount,0))=0 then 0         
 else (SUM(isnull(A.CHARGE_AMOUNT,0)) +sum(a.adjustment_amount))             
end as AdjustedCharge,
SUM(isnull(A.PAYMENT_AMOUNT,0)) AS PaymentAmount,
FROM ENDFILE AS EN 
 LEFT OUTER JOIN ENHFILE AS ENH ON EN.PATIENT_NO = ENH.PATIENT_NO AND EN.ENCOUNTER_NO = ENH.ENCOUNTER_NO  
 LEFT OUTER JOIN FIDFILE AS FIDR ON FIDR.PATIENT_NO = EN.PATIENT_NO AND FIDR.PAYOR_CLASS IN ('R','S','5','6') 
     AND FIDR.EFFECTIVE_DATE =(SELECT MAX(EFFECTIVE_DATE)
                                FROM FIDFILE F
                                WHERE (F.PATIENT_NO=FIDR.PATIENT_NO) AND (F.PAYOR_CLASS=FIDR.PAYOR_CLASS) 
                                 AND (F.PAYOR_NO=FIDR.PAYOR_NO) and (f.PRIORITY=fidr.PRIORITY)
                                 AND ((F.EFFECTIVE_END_DATE >= ENH.ENCOUNTER_DATE) OR (F.EFFECTIVE_END_DATE IS NULL)))
 LEFT OUTER JOIN AR00FILE AS R ON R.PATIENT_NO=EN.PATIENT_NO AND R.CHARGE_NO=EN.CHARGE_NO 
 LEFT OUTER JOIN ARFILE AS A ON A.PATIENT_NO=R.PATIENT_NO AND A.CHARGE_NO=R.CHARGE_NO 
     AND A.PAYOR_CLASS IN ('R','S','5','6')         
WHERE (ENH.ENCOUNTER_DATE >= '01-jan-2016')    
 AND (ENH.ENCOUNTER_DATE <= '31-mar-2016')    
GROUP BY EN.PATIENT_NO, EN.PAYOR_CLASS, A.CHARGE_NO, FIDR.INSURANCE_NO, ENH.ENCOUNTER_DATE, 
 EN.PROCEDURE_CODE, EN.PAYOR_CLASS, EN.PAYOR_NO, EN.UNITS, FIDR.PRIORITY

Msg 8120, Level 16, State 1, Line 22 Column 'FIDFILE.PATIENT_NO' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

0

The solution was the GROUP BY on the indicated column needed to be in the outer query.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.