b1234 b1234 - 15 days ago 9
SQL Question

Missing Keyword when trying to Select into

So I have a function to return an Average of a column such as

CREATE OR REPLACE FUNCTION avgCol
RETURN DEC IS avgNum DEC;
BEGIN
SELECT AVG(myCol)
INTO avgNum
FROM MyTable;
RETURN avgNum;
END;
/


While trying to test the results, i have the following

SELECT avgCol
INTO RESULT
FROM DUAL;


but it gives me the error

ORA-00905: missing keyword
00905. 00000 - "missing keyword"
*Cause:
*Action:
Error at Line: 175 Column: 6


Where line 175 is
INTO RESULT
. As far as I know, this is a scalar function and I'm trying to return a signal variable so it should work right? What keyword am I missing here?
Also I know I can just use AVG(), but I am learning how to create a scalar function. this is strictly for learning purposes.

Answer

While testing your code (which should be ok), you need

SELECT avgCol AS result FROM DUAL;