Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Der all
I have got the following expression in a my script
Now I want to change det_ref_date and det_risk_date of '13-mar-18' I want to use a variable.
SELECT DET_DEBT_CODE,DEB_NAME,DET_BRANCH_CODE,det_rp_code,
SUM(CASE WHEN TO_date(:P_AS_AT_DATE,'dd-mon-yy') - to_date(REF_C_DATE) BETWEEN 0 AND 30 THEN NVL(txn_amt,0) END ) Days_0_30,
SUM(CASE WHEN TO_date(:P_AS_AT_DATE,'dd-mon-yy') - to_date(REF_C_DATE) BETWEEN 31 AND 60 THEN NVL(txn_amt,0) END ) Days_31_60,
SUM(CASE WHEN TO_date(:P_AS_AT_DATE,'dd-mon-yy') - to_date(REF_C_DATE) BETWEEN 61 AND 90 THEN NVL(txn_amt,0) END ) Days_61_90,
SUM(CASE WHEN TO_date(:P_AS_AT_DATE,'dd-mon-yy') - to_date(REF_C_DATE) BETWEEN 91 AND 120 THEN NVL(txn_amt,0) END ) Days_91_120,
SUM(CASE WHEN TO_date(:P_AS_AT_DATE,'dd-mon-yy') - to_date(REF_C_DATE) BETWEEN 121 AND 150 THEN NVL(txn_amt,0) END ) Days_121_150,
SUM(CASE WHEN TO_date(:P_AS_AT_DATE,'dd-mon-yy') - to_date(REF_C_DATE) BETWEEN 151 AND 180 THEN NVL(txn_amt,0) END ) Days_151_180,
SUM(CASE WHEN TO_date(:P_AS_AT_DATE,'dd-mon-yy') - to_date(REF_C_DATE) BETWEEN 181 AND 360 THEN NVL(txn_amt,0) END ) Days_181_360,
SUM(CASE WHEN TO_date(:P_AS_AT_DATE,'dd-mon-yy') - to_date(REF_C_DATE) >360 THEN NVL(txn_amt,0) END ) Days_Over_360
FROM (
select a.DET_DEBT_CODE,
( select DEB_NAME_1 from ac_m_debtor where DEB_BRANCH_CODE=a.DET_BRANCH_CODE
and DEB_DEBTOR_CODE=a.DET_DEBT_CODE ) DEB_NAME,
a.DET_BRANCH_CODE,
a.DET_REF_C ,
det_risk_date REF_C_DATE,
a.det_contra_acct DET_REF_DATE,
( select sum(decode(det_dc_code,'D',det_hc_amt,'C',-det_hc_amt)) from ac_t_debtor c where a.det_ref_c=c.det_ref_c
and a.det_branch_code=c.det_branch_code and c.det_ref_date<=TO_date(:P_PROG_DATE,'dd-mon-yy') ) Txn_Amt,
a.DET_TXNTYPE,
a.DET_REF_2,
A.det_rp_code
from ac_t_debtor a
where a.det_ref_date<=TO_date(:P_AS_AT_DATE,'dd-mon-yy')
---and A.DET_DEBT_CODE between :P_CR_FROM and :P_CR_TO
and a.det_risk_date<=TO_date(:P_AS_AT_DATE,'dd-mon-yy')
and a.det_txntype='1'
and a.DET_BRANCH_CODE LIKE 'CO%'
order by A.DET_DEBT_CODE
)
WHERE txn_amt !=0
GROUP BY DET_DEBT_CODE,DEB_NAME,DET_BRANCH_CODE,det_rp_code
ORDER BY DET_DEBT_CODE;
saga
In your script set your variable:
LET vdet_risk_date = Date(Now(),'DD-MMM-YY');
In your SQL:
and a.det_risk_date<='$(vdet_risk_date)'
Let vDate=Date(Date#('13-mar-18','DD-MMM-YY'),'DD-MMM-YY');
where a.det_ref_date<='$(vDate)'
and a.det_risk_date<='$(vDate)'
