CODEWITHSUNDEEP
sqloracle-databasefunction
pepe12362
pepe12362

Reputation: 1

ORACLE SQL ORA-24344 - counting attendance function error

Hey guys I have a problem, I want to create a function in ORACLE which will count number of attendance (how many subject has enrolled one student). Could you give me a tip I don't know if my syntax is even okay?

CREATE OR REPLACE FUNCTION num_of_attendance (id_student IN NUMBER)
RETURN NUMBER
IS total_number_of_attendance NUMBER DEFAULT 0;
BEGIN 
    WITH attendance AS
    (
        SELECT id_student, code_course, COUNT(DISTINCT code_subject)
        FROM STUDENT NATURAL INNER JOIN attendance NATURAL INNER JOIN course NATURAL INNER JOIN course_subject NATURAL INNER JOIN subject
    );
    RETURN total_number_of_attendance;
END;
/

Upvotes: 0

Views: 52

Answers (1)

pifor
pifor

Reputation: 7892

First try to run the SQL statement outside the function.

Does this SQL statement runs without error ?

WITH attendance AS
    (
        SELECT id_student, code_course, COUNT(DISTINCT code_subject)
        FROM STUDENT NATURAL INNER JOIN attendance NATURAL INNER JOIN course NATURAL INNER JOIN course_subject NATURAL INNER JOIN subject
    );

Second, if you need to return a value from a function you need to store this value using INTO variable clause with this general syntax:

SELECT ... INTO variable FROM ... WHERE ...

Upvotes: 1

Related Questions