Home » SQL & PL/SQL » SQL & PL/SQL » updating table banktransactions with pl-sql
| updating table banktransactions with pl-sql [message #429473] |
Wed, 04 November 2009 01:43  |
chrispl/sql
Messages: 3
Registered: November 2009 |
Junior Member |
|
|
Hi everybody,
I have got a pl/sql question:
I have got two tables (bank and mutation).
Mutation
Nr From_bank To_bank Amount Date Sort_tr Code
1 1 1 250 04-01-94 A 0
2 2 2 500 05-01-94 A 0
3 3 3 150 06-01-94 A 0
4 4 5 250 07-01-94 O 0
5 1 3 550 10-01-94 O 0
6 4 4 350 11-01-94 B 0
7 5 5 650 12-01-94 B 0
8 5 1 250 13-01-94 O 0
9 2 4 50 14-01-94 O 0
10 6 1 750 17-01-94 O 0
11 3 1 1750 18-01-94 O 0
12 4 4 75 19-01-94 O 0
Bank
Bank_ac Sal Date
1 1000 31-12-93
2 1000 31-12-93
3 1000 31-12-93
4 1000 31-12-93
5 1000 31-12-93
Sort_tr:
A = withdraw
B = payment
O = transfer
Code:
0 = not yet done
1 = done
2 = amount not enough
3 = bankaccount not known
4 = transfer to own bankaccount not possible
5= else....
I need to proces the transfers to and from the different bankaccount to get this result:
Bank_ac Sal date
1 450 13-jan-94
2 450 14-jan-94
3 1400 10-jan-94
4 1150 14-jan-94
5 1650 13-jan-94
Nr From_bank To_bank Amount Date Sort_tr Code
1 1 1 250 04-jan-94 A 1
2 2 2 500 05-jan-94 A 1
3 3 3 150 06-jan-94 A 1
3 4 5 250 07-jan-94 O 1
5 1 3 550 10-jan-94 O 1
6 4 4 350 11-jan-94 B 1
7 5 5 650 12-jan-94 B 1
8 5 1 250 13-jan-94 O 1
9 2 4 50 14-jan-94 O 1
10 6 1 750 17-jan-94 O 3
11 3 1 1750 18-jan-94 O 2
12 4 4 75 19-jan-94 O 4
But when I look at it, my head starts to spin. I think I have to use 3 cursors...1 for withdraw, 1 for payment and 1 for transfer but I don't know where to start.
Could you give me some pointers on how to get started or maybe you've got an example.
Thanks a million,
Here are the create and insert table statements:
create table bank
(bank_ac number(9) NOT NULL,
sal number(9,2) NOT NULL,
date date NOT NULL);
create table mutation
(nr number(4) NOT NULL,
from_bank number(9) NOT NULL,
to_bank number(9) NOT NULL,
amount number(9,2) NOT NULL,
date date NOT NULL,
sort_tr varchar2(1) NOT NULL,
code number(1) NOT NULL);
insert into bank (bank_ac, sal, date)
values(1, 1000, '31-dec-93');
insert into bank (bank_ac, sal, date)
values(2, 1000, '31-dec-93');
insert into bank (bank_ac, sal, date)
values(3, 1000, '31-dec-93');
insert into bank (bank_ac, sal, date)
values(4, 1000, '31-dec-93');
insert into bank (bank_ac, sal, date)
values(5, 1000, '31-dec-93');
insert into mutation (nr, from_bank, to_bank, amount, date, sort_tr, code)
values(1, 1, 1, 250, '04-jan-94', 'A', 0);
insert into mutation (nr, from_bank, to_bank, amount, date, sort_tr, code)
values(2, 2, 2, 500, '05-jan-94', 'A', 0);
insert into mutation (nr, from_bank, to_bank, amount, date, sort_tr, code)
values(3, 3, 3 , 150, '06-jan-94', 'A', 0);
insert into mutation (nr, from_bank, to_bank, amount, date, sort_tr, code)
values(4, 4, 5 , 250, '07-jan-94', 'O', 0);
insert into mutation (nr, from_bank, to_bank, amount, date, sort_tr, code)
values(5, 1, 3 , 550, '10-jan-94', 'O', 0);
insert into mutation (nr, from_bank, to_bank, amount, date, sort_tr, code)
values(6, 4, 4 , 350, '11-jan-94', 'B', 0);
insert into mutation (nr, from_bank, to_bank, amount, date, sort_tr, code)
values(7, 5, 5 , 650, '12-jan-94', 'B', 0);
insert into mutation (nr, from_bank, to_bank, amount, date, sort_tr, code)
values(8, 5, 1 , 250, '13-jan-94', 'O', 0);
insert into mutation (nr, from_bank, to_bank, amount, date, sort_tr, code)
values(9, 2, 4 , 50, '14-jan-94', 'O', 0);
insert into mutation (nr, from_bank, to_bank, amount, date, sort_tr, code)
values(10, 6, 1 , 750, '17-jan-94', 'O', 0);
insert into mutation (nr, from_bank, to_bank, amount, date, sort_tr, code)
values(11, 3, 1 , 1750, '18-jan-94', 'O', 0);
insert into mutation (nr, from_bank, to_bank, amount, date, sort_tr, code)
values(12, 4, 4 , 75, '19-jan-94', 'O', 0);
|
|
|
| Re: updating table banktransactions with pl-sql [message #429477 is a reply to message #429473] |
Wed, 04 November 2009 02:03  |
Michel Cadot
Messages: 29436
Registered: March 2007
Location: Nanterre, France, http://... |
Senior Member |
|
|
Can you explain the result you want? What are sort_tr and code? How are they calculated?
What are the current bank table values? The starting point?
What are the result bank table values? The end point? Explain the Sal values.
And please align the columns in result, you can use code tags for it also.
Regards
Michel
[Updated on: Wed, 04 November 2009 02:04]
|
|
|
| Re: updating table banktransactions with pl-sql [message #429517 is a reply to message #429473] |
Wed, 04 November 2009 05:20 |
idris.ali
Messages: 30
Registered: June 2008
Location: Hyderabad |
Member |
|
|
Hi,
You may write a plsql to get the desired result using following cursors
C_1. Fetch the total of transferred amount from each bank account
select from_bank,sum(amount) debit_amt,max(date) last_date
from Mutation
where Sort_tr = 'O'
and code = 1
group by from_bank;
C_2. Fetch the total of transferred amount TO each bank account
select to_bank,sum(amount) credit_amt,max(date) last_date
from Mutation
where Sort_tr = 'O'
and code = 1
group by to_bank;
C_3. Fetch the amount withdrawn or payed from each bank account.
select from_bank,sum(amount) debit_amt,max(date) last_date
from Mutation
where sort_tr in ('A','B')
and code = 1
and from_bank = to_bank
group by from_bank;
Then
Loop Thru C1
update bankaccount
set sal = sal - c1.debit_amount,
date = greatest(date,c1.last_date)
where bank = c1.to_bank
Similarly subtract the debit amount and Add the credit amount to SAL for C2 , C3 also
Thanks,
Idris
[Updated on: Wed, 04 November 2009 05:22]
|
|
| |
| Re: updating table banktransactions with pl-sql [message #429535 is a reply to message #429473] |
Wed, 04 November 2009 07:42 |
chrispl/sql
Messages: 3
Registered: November 2009 |
Junior Member |
|
|
Wow thanks Idris
I will try to complete this script tomorrow...I will post back the results.
@Michel Cadot:
The starting point are the first two tables
The desired result are the last two tables
The sort_tr is the kind of transaction. So withdraw is minus....etc...
The code is a kind of exception...so if one hasn't got enough money on his/her bankaccount to withdraw 1000 dollars.....the transfer can't take place and a code 2 has to be inserted (or updated as you will) into the table bank
|
|
| |
| Re: updating table banktransactions with pl-sql [message #429679 is a reply to message #429473] |
Thu, 05 November 2009 03:32 |
chrispl/sql
Messages: 3
Registered: November 2009 |
Junior Member |
|
|
Hi Michel,
Well..it's not that hard:
I've got bank_account number 1. As you can see in the table mutation...I withdraw (A) 250 dollars on january 4th. So the table bank has to be updated. My salary in table bank has to be decreased by 250 dollars, the date has to be updated and this is a valid transfer...so the table mutation, column has to be updated with code 1 (done)
Let's say...you've got bank_account number 4. You got 1000 dollars on your account on december 31. You want to withdraw (A) 1500 dollars. This isn't possible (obviously). So only the table mutation, column code has to be updated with 2 (not enough)
Ok.....and you want to transfer some money by internet. But instead of typing a different bank_account as a recipient, you type your own (bank_account number 4). This isn't possible and so the table mutation, column code has to be updated with 4 (transfer to own bank_account not possible)
I hope this is a better explination and I'm very curious about your script
Hope you will post
|
|
| |
Goto Forum:
Current Time: Tue Dec 1 19:33:10 CST 2009
Total time taken to generate the page: 0.20872 seconds
|
] 
Members
Search
Help
Register
Login
Home
