case when 实例

select distinct am.accountno,am.accountname,o.orgname,bs.name,vratB.Direction, bsc.currencyname,vratB.Amount,vratB.OppAccountNo,vratB.OppAccountName,vratB.OppBranchName, vratB.TransActionTime,vratB.ABSTRACTINFO,bsa.purposename,am.accountinway,vratB.id

from v_rp_acct_transinfo vratA,v_rp_acct_transinfo vratB,bs_organization o,am_account am,bs_bank bs,bs_currency bsc, bs_accountpurpose bsa

where am.id=vratB.ACCOUNTID
and am.orgid = o.id
and bsc.id=vratB.CurrencyID
and bs.id=am.bankid
and vratA.AMOUNT = vratB.Amount
and vratA.Direction=1
and vratB.Direction=2
and vratA.ACCOUNTID = vratB.ACCOUNTID
and nvl((case when vratA.bankType = 'ABC'
then substr(vratA.OppAccountNo, -16,16)
else vratA.OppAccountNo end),
(case when vratB.bankType = 'ABC' then substr (vratB.OppAccountNo, -16,16) else vratB.OppAccountNo end)) =
nvl((case when vratB.bankType = 'ABC'
then substr(vratB.OppAccountNo, -16,16)
else vratB.OppAccountNo end),
(case when vratA.bankType = 'ABC' then substr (vratA.OppAccountNo, -16,16) else vratA.OppAccountNo end))
and o.status=1 and bs.status=1
and bsc.status=1
and bsa.status=1
and bsa.id=am.accountpurposeid
and am.status=1 and o.id=1
and trunc(vratB.TransActionTime)>=
to_date('2010-06-21', 'yyyy-mm-dd')
and trunc(vratA.TransActionTime)>=
to_date('2010-06-21', 'yyyy-mm-dd')
and trunc(vratB.TransActionTime)<=
to_date('2010-07-21', 'yyyy-mm-dd')
and trunc(vratA.TransActionTime)<=
to_date('2010-07-21', 'yyyy-mm-dd')
and ((trunc(vratB.TRANSACTIONTIME)-trunc(vratA.TRANSACTIONTIME))>=-3
and (trunc(vratB.TRANSACTIONTIME)-trunc(vratA.TRANSACTIONTIME))<=3)
order by vratB.transactiontime desc,vratB.id desc