SELECT AGGREGATE ERROR

Database
Enthusiast

SELECT AGGREGATE ERROR

Getting teradata error that 'select non-aggregate values.. blah, blah" however  all of my SELECT fields

are in my GROUP BY statement.. what gives?

***************************************

Select P.Pat_Id

,'Active' as Patient_Status

,cbo.lob_name

,cbo.product_type

,cbo.payor_name

,cbo.coverage_type

,cbo.mem_cvg_eff_date 

,cbo.mem_cvg_term_date 

,cbo.mem_rel_to_guar_c

,cbo.mem_covered_yn

From Panel_Members P

Inner Join Patient A on P.Pat_Id=a.Pat_Id 

and A.Pat_Status_C='1'

inner join     pat_cvg_ben_ot cbo ON a.pat_id = cbo.pat_id and cbo.coverage_active_yn = 'y'

and (cbo.mem_cvg_term_date > current_date or cbo.mem_cvg_term_date is null) --Active Coverage

left outer join pat_enc_rsn_visit e on a.pat_id = e.pat_id 

and e.enc_reason_name = 'death note'

Where e.pat_id is null 

Group by P.Pat_Id

,Patient_Status

,cbo.lob_name

,cbo.product_type

,cbo.payor_name

,cbo.coverage_type

,cbo.mem_cvg_eff_date 

,cbo.mem_cvg_term_date 

,cbo.mem_rel_to_guar_c

,cbo.mem_covered_yn

Qualify Rank() Over (Partition by a.pat_id

order by 

cbo.coverage_type desc

,cbo.payor_name

,cbo.mem_cvg_eff_date desc

,cbo.mem_cvg_term_date desc

,cbo.mem_rel_to_guar_c 

) = 1

1 REPLY
Senior Apprentice

Re: SELECT AGGREGATE ERROR

If a column Patient_Status exists the parser will use this instead of the alias "Patient_Status".

So use a different alias or GROUP BY 1,2,3,4,5,6,7,8,9,10 instead.