Functions and Views [message #37039] |
Fri, 11 January 2002 08:15 |
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.
|
|
|
|