Home » SQL & PL/SQL » SQL & PL/SQL » Functions and Views
Functions and Views [message #37039] Fri, 11 January 2002 08:15 Go to next message
Scott Kiskis
Messages: 2
Registered: January 2002
Junior Member
I am having some problems with a calculated field in a Crystal Report I am editing that is based on several Oracle views. The problem is that a sum function is being run in the report on a field that contains more than one value. However, when run, the sum function in Crystal brings back the wrong total.

I have attempted the same thing using the sum function attached to a SELECT statement for the field I want to query and I have gotten the proper total. Unfortunately, you can't run a SUM function in a view so I decided to use a function. The only problem is now, the function adds up the first set of values in a resulting recordset when it should add the first, second, etc. together.

My function is as follows:

FUNCTION PERMIT_FEES(TOTAL_FEES BP_PERMIT_FEES.TOTAL_PAID_AMOUNT%TYPE)
RETURN NUMBER
IS
V_RESULT_FEE NUMBER;

BEGIN
SELECT SUM(TOTAL_PAID_AMOUNT)
INTO V_RESULT_FEE
FROM BP_PERMIT_FEES
WHERE TOTAL_PAID_AMOUNT = TOTAL_FEES;

RETURN V_RESULT_FEE;
END PERMIT_FEES;

Any help would be appreciated.
Re: Functions and Views [message #37042 is a reply to message #37039] Fri, 11 January 2002 10:44 Go to previous message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
post your query also..
Previous Topic: FOR???
Next Topic: D2K
Goto Forum:
  


Current Time: Tue Mar 31 10:56:59 CDT 2020