select gcd.oasysFundingSumSeq.nextval, ptrsid, portfoliocusip, deal,transactionId, currency, spotrate, -cititotal, asofdate
from (
select p.ptrsid, v.portfoliocusip, o.deal, o.transactionId,
o.currency, PKG_ASSET.getlastfxrate(o.currency, v.currency, trunc(?)) as spotrate, sum(o.amount) cititotal, o.asofdate
from oasysfunding o right outer join ptrsoasys p on o.deal = p.oasysdealid and o.transactionId=(case when p.oasystransactionId is not null then p.oasystransactionId else o.transactionId end)
left outer join pvtrs v on p.ptrsid = v.pvtrsid
where o.asofdate = trunc(?) and p.transactiontype='FUNDING'
group by ptrsid, portfoliocusip, deal,transactionId, o.currency, v.currency, o.asofdate
order by ptrsid, deal, o.currency
use case when on the outer join condition
最新推荐文章于 2024-01-03 11:50:51 发布