Aggregate & Case

UDA
Enthusiast

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
2 REPLIES
Enthusiast

Re: Aggregate & Case

Hi,

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

Regards,
Balamurugan
Enthusiast

Re: Aggregate & Case

-- 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');