My requirement is as below......
First get a record from Table A and then get the record from Table B and do the runn_bal - rem_amt.
If runn_bal - rem_amt <> 0 then update the runn_bal with runn_bal - rem_amt an Table A , and get the second record from Table B and and do the math again...and again.until it reaches 0. When it reaches 0 stop the process of Table B and update the runn_bal=0 for the first record in Table A.
Then get the second record from Table A and get the third record from Table B and process.so on and so.......
Code: Select all
Note:
There is no relation between rcvbl_id and remtc id
Table A
rcvbl_id ano rcvbl_amt runn_bal status
1 1 254 254 0
2 1 3089 3089 0
3 1 2 2 0
Table B
remtc_id ano rem_amt status
1 1 254 0
2 1 3089 0
3 1 3089 0
4 1 2 0
5 1 2 0
the output should be
Table A
rcvbl_id ano rcvbl_amt runn_bal status
1 1 254 0 1
2 1 3089 0 1
3 1 2 -3091 1
Thanks in advance.