1. SQL Codes as follows:
–spool c:\Retail_Origination.txt
set linesize 5000
set pagesize 5000
set verify off
undefine FromDDMMYYYY
undefine ToDDMMYYYY
SELECT a.bra_loc_code, a.branch,NVL(amt,0), NVL(no, 0)
FROM M_BRANCH_MLS a ,
(SELECT bra_loc_code, branch,sum(APPROVED_LIMIT) amt, COUNT(*) no
FROM LOS_D_WOLOC_LOAN_DETAILS, M_BRANCH_MLS
WHERE LOAN_STATUS = ‘C’
AND loa_path_ref in (72,53727)
and SOURCE_PROD_TYPE_CD in (51438,51563,58355,58361,59336,60059,61225,61226,61227,61606)
AND TRUNC(AA_DATE) >= TRUNC(TO_DATE(’&&FromDDMMYYYY’, ‘ddmmyyyy’))
AND TRUNC(AA_DATE) <= TRUNC(TO_DATE(’&&ToDDMMYYYY’, ‘ddmmyyyy’))
AND branch_cd = BRANCH_CODE
AND AA_ID NOT IN (SELECT AA_ID
FROM LOS_D_WOLOC_PROPERTY_DETAILS
WHERE prp_slno = 1
)
GROUP BY bra_loc_code, branch) B
WHERE a.branch = b.branch(+)
ORDER BY bra_loc_code, branch
/
–spool off
2. Result
warning: linesize set to 2000 maximum (5000 specified)
BRANCH NVL(AMT,0) NVL(NO,0)
—————————————- —————- ————-
AFB(NON-MFC) 0 0
AIR HITAM 0 0
AL-IDRUS COM.CENTRE,KUCHING 0 0
ALAM DAMAI 0 0
ALAMESRA 0 0
ALMA 0 0
ALOR GAJAH 0 0
ALOR SETAR 0 0
ALOR SETAR - AFC 0 0
ALOR SETAR - AFC 0 0
10 rows selected.
Recent Comments