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
原创
©著作权归作者所有:来自51CTO博客作者mb649d3a75b51a2的原创作品,请联系作者获取转载授权,否则将追究法律责任
下一篇:棒球比赛规则要点

提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
left join 后用 on 还是 where?
sql语法
SQL sql 结果集 -
CASE WHEN用法
CASE
CASE