|
|
|
Supreme Being
Group: Forum Members
Last Login: 4/3/2008 12:29:54 PM
Posts: 102,
Visits: 81
|
|
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
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 6/25/2008 3:36:22 AM
Posts: 56,
Visits: 61
|
|
Hi,
Try using alias in all possible columns, also check if you are selecting the columns from the proper table.
Regards,
Balamurugan
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 4/3/2008 12:29:54 PM
Posts: 102,
Visits: 81
|
|
-- 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');
|
|
|
|