Hi,
Another long standing wish for Oracle to introduce parameterized views. Search for that to find the various ways for :
I have parameter set at the system...
One option (replace GetMode with your technique) :
SELECT sum(Amount), tgt_group_cd
from (
SELECT Amount, tgt_group_cd, tv.txn_id
from payments_view pv,transactions_view tv
where pv.tcd_id=tv.pmt_tgt_id
and GetMode = 1
union all
SELECT Amount,tgt_group_cd, tv.txn_id
from pmt_vw pv, txns_vw tv
where pv.tcd_id=tv.pmt_tgt_id
and GetMode = 0
)
where txn_id=CV_txn_Id
group by tgt_group_cd
may require some tuning effort.
regards,