Function not returning correct value for one parameter

[neetsw]

Hi,

I've created a function and if you run the function on a whole table eg
(select ActivityScaleValueConv_Funct(JobNumber) from WPERIODIC)
it returns the correct values for all the entries in the table,

But if you run it on one value eg
(select ActivityScaleValueConv_Funct(2222) from WPERIODICFINAL)
it returns a entry (the same) for all the entries in the table, and I need it to only return the value for the one entry I am looking for.

The function is at the bottom, any help will be appreciated.

The function looks as followes:

CREATE OR REPLACE FUNCTION ScaleValueConv_Funct (Job_Number IN Varchar2)
RETURN NUMBER
IS SCALEVAL NUMBER(20,2);
BEGIN
SELECT SUM(E.revenuerecognizedbas) INTO SCALEVAL
FROM JEntry E
WHERE E.JOBNUMBER = Job_Number
AND E.ActNumber = '9002'
AND E.FEDate < '&1';
RETURN(SCALEVAL);
END;

 

[lewisp]

Of course it will! You have no WHERE clause to prevent the same number of records being returned. Of course, for each row returned your SQL makes a call to the function using the same parameter value. So therfore you get multiple returns of exactly the same value.

What you need to do is something like

SELECT ActivityScaleValueConv_Funct(JobNumber)
FROM    WPERIODIC
WHERE   JobNumber = 2222;

 

[SantaMufasa]

...and as an addition to Lewis's correct response, remember, as a rule, if you have no WHERE clause in a SELECT statement, all rows from the table are in the result set. So, in your case, you effectively said, "Return all rows from the WPERIODIC table and for each row, display the function results for just the row whose JobNumber = 2222."

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:35 (24Aug04) UTC (aka "GMT" and "Zulu"), 11:35 (24Aug04) Mountain Time)