Home » Developer & Programmer » Forms » balance calculation et al
balance calculation et al [message #358650] Wed, 12 November 2008 01:12 Go to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Iam doing leave Management project. The fields included in my apply leave form is

leaveeentitleddays,NumberofHits,fromdate,todate,balanceleave..

I have given the coding as,

begin
if :empaply.numberofhits = 1 then
:empaply.balance:=:empaply.leaveentitleddays-:empaply.numberofdays;
else
select (balance - numberofdays)into :empaply.balance from empaply where :empaply.employeeid=:empaply.employeeid;


Here the else clause coding is not working.. empaply is the table name.

As given in the else clause code i want to calculate the balance leave from the existing balance in the database(the balance that was calculated and stored in the database in the previous record when the value of number of hits field is '1'.. as given in if clause coding)that is by subtracting number of days from existing balance in the database..And i want the resulting value to get assigned to the field balance of apply leave form..

Here everything has to happen within the same table but between two row of records ..

Is there any way to get this result?

Awaiting for your support..
Re: balance calculation [message #358917 is a reply to message #358650] Thu, 13 November 2008 00:04 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Have you heard of the 'space' and 'underscore' characters? I suggest using the first in your code and the second in your column names. Please format your code and post between 'code' tags.

The 'else' is not working because the new data does not yet exist in the database.

I strongly suggest NEVER storing calculated fields in your database. Calculate them EVERY time that you need them.

Also, use 'Calculation' fields in your form to handle your calculations.

David

Re: balance calculation [message #359159 is a reply to message #358917] Thu, 13 November 2008 23:28 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
//Edited version of my message
Sir
Iam doing leave Management project. The fields included in my apply leave form is

1. Leave Entitled Days
2. Number of Hits
3. From date
4. To date
5. Number of days
6. Balance leave

I have given the coding as,
begin 
 if :empaply.numberofhits = 1 then 
 :empaply.balance:=:empaply.leaveentitleddays-:empaply.numberofdays;
 else 
 select (balance - numberofdays)into :empaply.balance from empaply where :empaply.employeeid=’123123’;
end;

Here the else clause coding is not working... empaply is the table name.

As given in the else clause code i want to calculate the balance leave from the existing balance that is already stored in the database (the balance that was calculated and stored in the database in the previous record when the value of number of hits field is '1'.. as given in if clause coding) that is by subtracting number of days from existing balance in the database. And i want the resulting value to get assigned to the field balance of apply leave form...

Here everything has to happen within the same table but between two rows of records...

Is there any way to get this result?

Awaiting for your support...

If anybody know the solution kindly reply

Thanks in Advance
[EDITED by DJM: applied [code] tags]

[Updated on: Thu, 13 November 2008 23:33] by Moderator

Report message to a moderator

Re: balance calculation [message #359162 is a reply to message #359159] Thu, 13 November 2008 23:36 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
As you have asked the same questions again I assume that you want other people to supply their ideas.

David
Re: balance calculation [message #359503 is a reply to message #359162] Sun, 16 November 2008 23:45 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Sir,

I sent the revised version of the message as u advised…

Actually the previous has been stored in the database. I have checked that. But still the else clause Coding is not working.

Looking forward for your further support.

Thanks in Advance …

balance calculation [message #360553 is a reply to message #358650] Fri, 21 November 2008 06:18 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Sir,

As I already told, I am doing leave management project.

The fields included in my apply leave form is
Tablename:empaply

Leave Entitled Days
Number of Hits
Balance leave
Number of Days

Here I have got problem in calculating the balance leave.
The coding I used is:
<code>
begin
if :empaply.numberofhits = 1 then
:empaply.balance := 8- :empaply.numberofdays;
else
select leavebalance-numberofdays into :empaply.balance from empaply where
employeeid =:empaply.employeeid;
end if;
end;
</code>

I have created another field ‘leave balance’ for which the I assigned the value that is obtained in the balance variable
Which is, :empaply.leavebalance := :empaply.balance;

Now this coding is working when first two times when the employee applies leave. That is

Assume,

First time : Numberofdays :2 balance:8-2:6
So, this ‘6’ get assigned to leave balance variable also.

Second time:

The else clause coding is executed as:

Let numberofdays=2 balance:6-2=4;

This result also iam getting, but for the third time:

Let numberofdays=3 balance:4-3=1;

I need to get the above result but this is not coming and the error FRM 40735 and ORA 04122 is obtained…

Looking forward for your support!

Thanks ..

Re: balance calculation [message #360769 is a reply to message #360553] Mon, 24 November 2008 00:19 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please don't start new topics for existing problems. AND GET RID OF THE '<' AND '>' CRAP!! This is not HTML.

David
Re: balance calculation [message #360812 is a reply to message #360769] Mon, 24 November 2008 01:28 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Ok sir.. Please excuse me ..

what can be done for the issue i mentioned in the previous message?

Please Help !
Re: balance calculation [message #361066 is a reply to message #360812] Tue, 25 November 2008 00:04 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Please supply the text of messages as well as error code.

So you have received:
FRM-40735: %s trigger raised unhandled exception %s.
and
ora-04122:exact fetch returns more then requested number of rows.

What code do you have in your trigger? AND USE [code] TAGS!!!

David
Re: balance calculation [message #361101 is a reply to message #361066] Tue, 25 November 2008 01:16 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Sir the coding i have used is ,

Trigger Name:WHEN_MOUSE_CLICK

[code]
begin
if :empaply.numberofhits = 1 then
:empaply.balance := 8- :empaply.numberofdays;
else
select leavebalance-numberofdays into :empaply.balance from empaply where
employeeid =:empaply.employeeid;
end if;
end;
[code]

Here with i have attached a screen shot where leave balance is the dummy field for which the value obtained in the balance is assigned by the coding

[code]:empaply.leavebalance=:empaply.balance[code]

And the leave entitled field is an optional field.

The button sum saves the entries of the field in database.

The button ret executes the query.

As u can see in the screen shot, the value of balance and leave balance in the third record remain the same as those of second record. Upto second record calculation goes well but for the third record it doesnt calculate and simply shows the value obtained in the previous record that is the second record..

pl help to solve this problem..

Thanks ..




  • Attachment: example.jpg
    (Size: 105.96KB, Downloaded 127 times)

[Updated on: Tue, 25 November 2008 05:47]

Report message to a moderator

Re: balance calculation [message #361334 is a reply to message #361101] Wed, 26 November 2008 00:13 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Listen numb-nuts!!! Practice using the 'code' tags in the 'Test' forum. There is a 'start' tag and an 'end' tag, They ARE DIFFERENT!!!

The trigger which is failing is in the DATABASE not the Form. What database triggers do you have on the table?

David
Re: balance calculation [message #361378 is a reply to message #361334] Wed, 26 November 2008 02:51 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Sir pl excuse me ... I wont repeat the mistake again.. Will use the code tags properly hereafter for sure..

And i didnt create or use any triggers in the database.. I have just created table there.

I have used WHEN_MOUSE_CLICK trigger in the forms.


Thanks ..

[Updated on: Wed, 26 November 2008 02:53]

Report message to a moderator

Re: balance calculation et al [message #361444 is a reply to message #358650] Wed, 26 November 2008 07:42 Go to previous messageGo to next message
ganesh_jadhav0509
Messages: 63
Registered: May 2007
Location: Chester
Member

We can not understand with only this much code
pls check the value of :empaply.numberofhits with useing message

Regards,

Ganesh Jadahv

Re: balance calculation et al [message #362204 is a reply to message #361444] Mon, 01 December 2008 05:38 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
Hi Ganesh !

As i sent in my previous message iam doing leave management project.
In tat i need to calculate balance leave for each employee after he applied the leave.

first time when he applies leave the balance leave must be calculated as 8 -currentnumberofdays

Otherwise it must be calculated from the existing balance that is the value obtatained when the employee applied leave for first time.

Balance=previousbalance-currentnumberofdays

This is the logic i need.

So please give a solution for this logic.

I dnt know how to check with message box...

Thanks for your Reply !
Re: balance calculation et al [message #362398 is a reply to message #362204] Tue, 02 December 2008 03:39 Go to previous messageGo to next message
varosh81
Messages: 178
Registered: February 2008
Location: CHENNAI
Senior Member
David sir waiting for your reply too !

regards,
varosh
Re: balance calculation et al [message #362417 is a reply to message #358650] Tue, 02 December 2008 04:08 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
You know your problem here looks almost identical to your problem in this thread:
http://www.orafaq.com/forum/t/127910/129190/.

You'll probably get your problem sorted a lot quicker if you stick to a single thread rather than spreading information across two.
[EDITED by DJM: remove 'email' tags]

[Updated on: Fri, 17 April 2009 02:36] by Moderator

Report message to a moderator

Previous Topic: Doubt regarding Vertical Scrollbar
Next Topic: how to open downloaded file
Goto Forum:
  


Current Time: Sat Dec 10 18:19:33 CST 2016

Total time taken to generate the page: 0.08771 seconds