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 Go to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 63809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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]

Report message to a moderator

Re: updating table banktransactions with pl-sql [message #429517 is a reply to message #429473] Wed, 04 November 2009 05:20 Go to previous messageGo to next message
idris.ali
Messages: 34
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]

Report message to a moderator

Re: updating table banktransactions with pl-sql [message #429523 is a reply to message #429517] Wed, 04 November 2009 05:39 Go to previous messageGo to next message
Michel Cadot
Messages: 63809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Idris,
As you are able to post a code, could you also answer my questions?

Regards
Michel
Re: updating table banktransactions with pl-sql [message #429535 is a reply to message #429473] Wed, 04 November 2009 07:42 Go to previous messageGo to next message
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 #429556 is a reply to message #429535] Wed, 04 November 2009 09:01 Go to previous messageGo to next message
Michel Cadot
Messages: 63809
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Too bad you didn't make the effort to explain more clearly I could provide you a better solution than previous PL/SQL code.
Well, it's up to you.

Regards
Michel
Re: updating table banktransactions with pl-sql [message #429679 is a reply to message #429473] Thu, 05 November 2009 03:32 Go to previous messageGo to next message
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

Re: updating table banktransactions with pl-sql [message #429939 is a reply to message #429679] Fri, 06 November 2009 06:59 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
As requested by the OP, an additional reply containing a script was removed.
Previous Topic: How to see the structure of a table apart from "desc table_name"?
Next Topic: looking for sql script guru.
Goto Forum:
  


Current Time: Thu Sep 29 02:13:35 CDT 2016

Total time taken to generate the page: 0.26660 seconds