Home » SQL & PL/SQL » SQL & PL/SQL » balance calculation (pl/sql ,10g,Windows XP)
balance calculation [message #360015] Wed, 19 November 2008 04:15 Go to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Sir,

I am doing leave management project. Various fields included in it are:
1. Number of Hits
2. Number of Days
3. Leave Entitled Days
4. Balance

I want to calculate the balance by number of days with 8-- When the value of number of hit is ‘1’ other wise balance has to be calculated by subtracting number of days from balance value that is already stored in the database (in the first record).Since it involves working with multiple records I used cursor concept.
The coding used is,
<code>
declare
cursor cc is
select balance,numberofdays,numberofhits from empaply;
cc1 empaply%rowtype;
vv1 number ;
vv2 number;
begin
for cc1 in cc loop
if cc1.numberofhits = &vv1 then
cc1.balance := (8 - cc1.numberofdays);
else
vv2 :=cc1.balance-cc1.numberofdays;
end if;
end loop;
end;
</code> … Here empaply is the table name.

But this is not working… Looking forward for your support..

Thanks in Advance !
Re: balance calculation [message #360018 is a reply to message #360015] Wed, 19 November 2008 04:28 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
A few points:

"this is not working" is not an oracle error. Post your formatted SQL session and tell us why you think it isn't working.

Working with multiple records and talking about "first records" is meaningless when you never give an order by in the select, since there are no "first records" in a relational database.

Have a look at the lead and lag functions, from the pretty vague description I think this is what you are after.


Re: balance calculation [message #360022 is a reply to message #360018] Wed, 19 November 2008 04:46 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Sir

I am new to pl sql. Actuallly first i tried this by using select..into statement since i got to calculate the balance in the second record from the value that is stored in the second record. I checked with the database, the first record is very much stored ther. But i got an ORA -01422 error.For which the solution i got from google as "Using Cursors". I have the sent the cursor coding that i tried in my previous message. Pl help me with the concept that should be used for my case.

Thanks for your immediate support.. Waiting for your further support !
Re: balance calculation [message #360023 is a reply to message #360022] Wed, 19 November 2008 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-01422: exact fetch returns more than requested number of rows
 *Cause: The number specified in exact fetch is less than the rows returned.
 *Action: Rewrite the query or change number of rows requested

Do you think we know all error codes by heart? Always post the full message text.

Also post a test case: create table and insert statements along with the result you want with these data and Oracle version with 4 decimals.

Regards
Michel

Re: balance calculation [message #360027 is a reply to message #360023] Wed, 19 November 2008 05:01 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Here is an example on how to use LAG:

SQL> CREATE TABLE test_tab (
  2    col1 VARCHAR2(1),
  3    col2 NUMBER);

Table created.
SQL>
SQL> INSERT INTO test_tab VALUES ('A',1 );
1 row created.
SQL> INSERT INTO test_tab VALUES ('B',5 );
1 row created.
SQL> INSERT INTO test_tab VALUES ('C',20);
1 row created.
SQL> INSERT INTO test_tab VALUES ('D',25);
1 row created.
SQL> INSERT INTO test_tab VALUES ('E',50);
1 row created.
SQL> INSERT INTO test_tab VALUES ('F',55);
1 row created.
SQL> INSERT INTO test_tab VALUES ('G',80);
1 row created.
SQL> INSERT INTO test_tab VALUES ('H',90);
1 row created.
SQL> INSERT INTO test_tab VALUES ('I',99);
1 row created.
SQL>
SQL> SELECT col1,
  2         col2,
  3         Lag(col2) over (ORDER BY col1),
  4         col2 - Lag(col2) over (ORDER BY col1)
  5    FROM test_tab;

C       COL2 LAG(COL2)OVER(ORDERBYCOL1) COL2-LAG(COL2)OVER(ORDERBYCOL1)
- ---------- -------------------------- -------------------------------
A          1
B          5                          1                               4
C         20                          5                              15
D         25                         20                               5
E         50                         25                              25
F         55                         50                               5
G         80                         55                              25
H         90                         80                              10
I         99                         90                               9

9 rows selected.

SQL>
SQL> DROP TABLE test_tab;
Table dropped.
SQL>


Post a test case for your requirement in the same way.
Re: balance calculation [message #360030 is a reply to message #360023] Wed, 19 November 2008 05:11 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
I am doing leave management project. Various fields included in it are:
1. Number of Hits
2. Number of Days
3. Leave Entitled Days
4. Balance


create table leave(employeeid number,numberofhits number,leavetype varchar2(20),Fromdate date,todate date,numberofdays number,balance
number);

insert into leave values(1001,1,'casual leave','10-nov-08',
'12-nov-08',3,5);

Here, number of days '3' is calculated by subtracting fromdate from todate that is ,(todate-fromdate). And balance '5' should be calculated by subtracting number of days from '8' that is,(8-numberofdays), 8-3=5. This should happen when the employee applies leave for the first time.

Second time,

insert into leave values(1001,2,'casual leave','15-nov-08',
'16-nov-08',2,3);

When the same employee logs in for the next time, the balance should be calculated by subtracting the current number of days from the balance that is stored previously(here it should subtract from 5).. Consider the second time the same employee applies for 2 days leave again then balance should be calculated as, 5-2=3.

Like this calculation should go on every the employee applies for leave..
Re: balance calculation [message #360032 is a reply to message #360027] Wed, 19 November 2008 05:16 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
cadot sir

waiting for your reply

[Updated on: Wed, 19 November 2008 05:20]

Report message to a moderator

Re: balance calculation [message #360054 is a reply to message #360032] Wed, 19 November 2008 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you think I'm only thinking about you and angerly waiting for you to answer my questions in order to answer you?

I'm still waiting for your test case.

Regards
Michel
Re: balance calculation [message #360058 is a reply to message #360032] Wed, 19 November 2008 07:08 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member
@varosh81,

Did you have the patience to go through the links and suggestions given by ThomasG?
ThomasG wrote on Wed, 19 November 2008 15:58
A few points:
Have a look at the lead and lag functions, from the pretty vague description I think this is what you are after.


Also, Please go through the examples posted earlier by ThomasG. Did you try some queries based on that or did you simply gave up on them?

Regards,
Jo
Re: balance calculation [message #360897 is a reply to message #360058] Mon, 24 November 2008 05:06 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Hi All,

I tried with the LEAD and LAG concept as suggested by ThomasG..But that didnt help our project...So i tried with cursor concept with the following coding:

declare
    cursor cc is
    select employeeid,numberofdays,numberofhits,balance
    from empaply where employeeid = 123123
    for update of balance;
    v1 number:= &v1;
    begin
    open cc;
    if  v1>1
    then
    update empaply set balance =balance-numberofdays 
    where current of cc;
     commit;
     end if;
    close cc;
 end;


But it gave the error ora-0410:Invalid Rowid

So pls help to overcome this error or suggest any other solutions to get the result i mentioned in my previous messages.

Thanks ..

[Mod-Edit: Frank added [code]-tags to improve readability.

[Updated on: Mon, 24 November 2008 05:26] by Moderator

Report message to a moderator

Re: balance calculation [message #360927 is a reply to message #360897] Mon, 24 November 2008 06:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do NOT commit inside the loop. (Not checked the program itself.)

Regards
Michel
Re: balance calculation [message #361038 is a reply to message #360927] Mon, 24 November 2008 22:55 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Sir I tried by giving COMMIT after the loop , still it is showing error…


SQL> declare
2 cursor cc is
3 select employeeid,numberofdays,numberofhits,balance
4 from empaply where employeeid = 123123
5 for update of balance;
6 v1 number:= &v1;
7 begin
8 open cc;
9 if v1>1
10 then
11 update empaply set balance =balance-numberofdays
12 where current of cc;
13 end if;
14 commit;
15 close cc;
16 end;
17 /
Enter value for v1: 3
old 6: v1 number:= &v1;
new 6: v1 number:= 3;
declare
*
ERROR at line 1:
ORA-01410: invalid ROWID
ORA-06512: at line 11


Please Help !

Re: balance calculation [message #361053 is a reply to message #360015] Mon, 24 November 2008 23:42 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
Sir I tried by giving COMMIT after the loop

Loop? Which loop? I do not see any loop in the code you posted. Moreover, there is no FETCH from the cursor in the code too. Then the error comes when you reference the non-fetched result.

Have a look into PL/SQL User's Guide and Reference, available with many other Oracle documentation books e.g. online on http://tahiti.oracle.com/. There are many examples how to loop through cursor results.
Re: balance calculation [message #362190 is a reply to message #361053] Mon, 01 December 2008 05:22 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Sir I tried with the following coding also, the result is not coming.The output window remains under processing for long time and nothing else is happening.

declare
cursor c_emp is select employeeid,numberofhits,numberofdays,balance from empaply where employeeid='123123';
str_emp_id empaply.employeeid%type;
num_NOH empaply.numberofhits%type;
num_NOD empaply.numberofdays%type;
num_bal empaply.balance%type;
begin
open c_emp;
loop 
fetch c_emp into str_emp_id,num_NOH,num_NOD,num_bal;
update empaply set balance=num_bal-num_NOD where numberofhits>1;
insert into bal values (num_bal);
end loop;
commit;
close c_emp;
end;


Pl help me with a solution !

Thanks !
Re: balance calculation [message #362193 is a reply to message #362190] Mon, 01 December 2008 05:26 Go to previous messageGo to next message
misragopal
Messages: 125
Registered: June 2005
Location: New Delhi, india
Senior Member

u forget EXIT CONDITION in loop.. Smile
Re: balance calculation [message #362208 is a reply to message #360015] Mon, 01 December 2008 05:46 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
First off you want to use a FOR LOOP. That way you don't need to code an exit for the loop.
Second - you appear to be updating the same empaply record(s) everytime, which would defeat the purpose of using a loop.
Re: balance calculation [message #362217 is a reply to message #362208] Mon, 01 December 2008 05:57 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Sir i tried in various aspects by giving exit when statement also but in vain.. So please assist with some sample coding !

[Updated on: Mon, 01 December 2008 05:59]

Report message to a moderator

Re: balance calculation [message #362220 is a reply to message #360015] Mon, 01 December 2008 06:19 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Like I said you don't need an exit condition, you need a FOR LOOP.
The Oracle documentation is really usefull you know:

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/sqloperations.htm#sthref1354

But before you go and rewrite your code to use a FOR LOOP ask yourself these questions:

1.How many rows on empaply statisfy this condition:
where employeeid='123123'


2. What is the relationship between the rows in empaply where this is true:
where employeeid='123123'

and the rows where this is true:
numberofhits>1


3. If you update empaply.balance to a value you've calculated, what happens to that value when you go round the loop a second time?
Re: balance calculation [message #362431 is a reply to message #362220] Tue, 02 December 2008 04:45 Go to previous message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
sorry sir

In a curiousity to get the solution soon, i messaged in two threads. Since i have a server problem here, i wasnt able to try the solution given by you. But iam going through the oracle docs for which u gave me the link.

Dont Mistake !

regards,
varosh
Previous Topic: Cursor
Next Topic: SPACE IN PADDING
Goto Forum:
  


Current Time: Mon Dec 05 06:42:33 CST 2016

Total time taken to generate the page: 0.10317 seconds