with new_balance_transaction as (select bt.owner_id, bt.owner_type,bt.payment_type,
bt.balance_amount,bt.commision_amount, bt.COMMISION_RATE, bt.status,bt.transaction_type,bt.create_time,
(case when bt.status=0 then bt.balance_amount else 0 end) invlid_total_balance_amount,
(case when bt.status=1 then bt.balance_amount else 0 end) vlid_total_balance_amount,
(case when bt.status=0 then bt.commision_amount else 0 end) invlid_total_commision_amount,
(case when bt.status=1 then bt.commision_amount else 0 end) vlid_total_commision_amount
from balance_transactions bt where bt.owner_id is Not null
and bt.create_time >= to_timestamp('2014-09-01 00:00:00.000000','yyyy-mm-dd hh24:mi:ssxff')
and bt.create_time <= to_timestamp('2014-09-30 23:59:59.999999','yyyy-mm-dd hh24:mi:ssxff') ),
balance_transaction_report as (
select o.login_name as owner, nbt.owner_type, nbt.payment_type,
nbt.balance_amount,nbt.commision_amount,nbt.COMMISION_RATE,
nbt.status,nbt.transaction_type,nbt.create_time,
nbt.invlid_total_balance_amount,nbt.vlid_total_balance_amount,
nbt.invlid_total_commision_amount,nbt.vlid_total_commision_amount
from new_balance_transaction nbt, operator o
where nbt.owner_id = o.operator_id and nbt.owner_type =1
union all
select m.merchant_name as owner,
nbt.owner_type, nbt.payment_type,
nbt.balance_amount,nbt.commision_amount,
nbt.COMMISION_RATE, nbt.status,nbt.transaction_type,
nbt.create_time, nbt.invlid_total_balance_amount,nbt.vlid_total_balance_amount,
nbt.invlid_total_commision_amount,nbt.vlid_total_commision_amount
from new_balance_transaction nbt, merchant m where nbt.owner_id = m.merchant_id and nbt.owner_type =2 )
select brr.owner,brr.owner_type,brr.payment_type, brr.balance_amount,
brr.commision_amount,brr.COMMISION_RATE,brr.status,brr.transaction_type,brr.create_time,
brr.invlid_total_balance_amount,brr.vlid_total_balance_amount,
brr.invlid_total_commision_amount, brr.vlid_total_commision_amount from balance_transaction_report brr
order by brr.create_time DESC
Oracle With As 用法
最新推荐文章于 2022-07-04 11:22:52 发布