All Forums UDA
marcmc 112 posts Joined 12/05
31 Mar 2008
Aggregate & Case

I need to remove the Year_Id in the where clause and include it in the CASE stmt for performance reasons. I'm merging alot of code into 1 statement and this is my first go at this.However, I'm selecting a sum aggregate and the Year Id is a constant variable.I receive the 3504 syntax error: Selected non-aggregate values must be part of the associated group.Any idea how I could get around this? SELECT (CASE WHEN Year_id = 2008 THEN (CAST((COALESCE(sum(f_basic_premium * F_exch_rate),0)) AS NUMERIC(18,6))) END),-- INTO :v_PremYTD FROM fat_bse_po_risk_detail a JOIN trt_lu_trans_subtype b ON a.Tr_sub_type_id = b.Tr_sub_type_id JOIN tit_lu_day c ON a.cur_trn_dt = c.cur_trn_dt JOIN POt_lu_policy d ON a.Policy_id = d.Policy_id WHERE Tr_type_id NOT IN ('HDQ', 'HFQ','HMQ','HRQ','IEQ','ILP','IQT','IPR') AND Po_corp_unit_id in ('GEI', 'GNI', 'GED');-- AND Year_id = 2008

Balamurugan B 81 posts Joined 09/07
31 Mar 2008

Hi,Try using alias in all possible columns, also check if you are selecting the columns from the proper table.Regards,Balamurugan

Regards,
Balamurugan

marcmc 112 posts Joined 12/05
31 Mar 2008

-- this is how. SELECT sum(CASE WHEN Year_id = 2008 THEN (CAST((COALESCE(f_basic_premium * F_exch_rate,0)) AS NUMERIC(18,6))) END), sum(CASE WHEN Week_id = 200749 THEN (CAST((COALESCE(f_basic_premium * F_exch_rate,0)) AS NUMERIC(18,6))) END)-- INTO :v_PremYTD, :v_PremTW FROM fat_bse_po_risk_detail a JOIN trt_lu_trans_subtype b ON a.Tr_sub_type_id = b.Tr_sub_type_id JOIN tit_lu_day c ON a.cur_trn_dt = c.cur_trn_dt JOIN POt_lu_policy d ON a.Policy_id = d.Policy_id WHERE Tr_type_id NOT IN ('HDQ', 'HFQ','HMQ','HRQ','IEQ','ILP','IQT','IPR') AND Po_corp_unit_id in ('GEI', 'GNI', 'GED');

You must sign in to leave a comment.