Hi All
I am using Oracle 11g Release 2
There are two tables named Customer_master_all and ATM_TGT
Structure and sample record of both the tables is furnished below.
CUSTOMER_MASTER_ALL---Table
CUST_NO Number(12)
POSTCODE Varchar2(8)
ATM_TGT---Table
TERMNID Varchar2(15)
PIncode Number(12)
Sample Records: CUSTOMER_MASTER_ALL
| CUST_NO | POSTCODE |
| 10174439 | 394221 |
| 10174442 | 396001 |
| 10174444 | 396001 |
| 10174456 | 396001 |
| 20174410 | 396001 |
| 76174401 | 396001 |
| 43174411 | 396001 |
| 10266239 | 394221 |
| 45674478 | 394221 |
| 64173939 | 403001 |
| 67874410 | 403001 |
| 51174480 | 403001 |
| 68698723 | 403001 |
| 90845431 | 403001 |
| 62098512 | 403001 |
| 67453281 | 333001 |
| 87201981 | 333001 |
| 11875302 | 333001 |
| | |
Sample Records--ATM_TGT
| TERMNID | PinCode |
| SC038101 | 394221 |
| SN000579 | 396001 |
| SN000577 | 396001 |
| SN000578 | 396001 |
| SW037001 | 396001 |
| SC075701 | 396001 |
| SN000583 | 396001 |
| SN000582 | 394221 |
| SW073001 | 394221 |
| SN000581 | 403001 |
| SW027101 | 403001 |
| SN000580 | 403001 |
| SN000587 | 403001 |
| SN000586 | 403001 |
| SN018602 | 403001 |
| SN000585 | 333001 |
Now my requirement is to get the TERMINAL IDs based on the Postcode/Pincode.
I came up with the below select statement but didn't get the desired output.
Select Qry1.CUST_NO,Qry1.PINCODE ,decode (Qry1.rn,1,Qry1.TERMNID) as ATM_ID1,
decode (Qry1.rn,2,Qry1.TERMNID,null) as ATM_ID2,
decode (Qry1.rn,3,Qry1.TERMNID,null) as ATM_ID3,
decode (Qry1.rn,4,Qry1.TERMNID,null) as ATM_ID4,
decode (Qry1.rn,5,Qry1.TERMNID,null) as ATM_ID5
from
(Select CUST.Cust_No, atm.PinCode,atm.TERMNID,ROW_NUMBER() Over (Partition by atm.TERMNID order by CUST.Cust_No,atm.Pincode) rn
from CUSTOMER_MASTER_ALL CUST,ATM_TGT atm
where CUST.POSTCODE=atm.PINCODE
) Qry1
where rn<=5
Client is expecting the output in the below format.
I would appreciated if you gurus can be help me to resolve the above mentioned problem.
Regards
Vinny