I think this will put your existing logic in a form that can execute without errors:
select vt.account_sub
,vt.cusip_id
,sum(case
when tc.test='unrealized_gain1_sch3a' then vt.cost_ps
when tc.test='unrealized_gain1_sch3a' and vt.cost_ps > 0 then vt.cost_ps
end) as unrealized_gain
from vw_perac_transaction as vt
left outer join tbl_maint_transactions_trans_trans_type_cd tc
on tc.transaction_type_cd = vt.transaction_type_cd
group by vt.account_sub
,vt.cusip_id
However, it looks like the logic in your case statements is funky, this slightly simplified statement should return the same results:
select vt.account_sub
,vt.cusip_id
,sum(case when tc.test='unrealized_gain1_sch3a' then vt.cost_ps end) as unrealized_gain
from vw_perac_transaction as vt
left outer join tbl_maint_transactions_trans_trans_type_cd tc
on tc.transaction_type_cd = vt.transaction_type_cd
group by vt.account_sub
,vt.cusip_id
Be One with the Optimizer
TG
sub select inside a sum function
Posted - 11/20/2006 : 15:14:49
