I am typing a function and i'm having an error here, I dont know what it is. Could you give me a Hand ?
CREATE or replace FUNCTION function1(pIdReg in number,pIdPeriod in number) RETURN
number
IS
ncv number DEFAULT 0;
BEGIN
SELECT COUNT(IdPeriod)
INTO ncv
FROM(
SELECT a.IdPeriod, SUM(case when a.nt=0 then -a.valor else a.valor end) AS total --IF(a.nt=0,-a.valor,a.valor))
FROM dc a
JOIN emp b ON a.idDoc = b.idDoc
WHERE a.idReg = pIdReg AND a.IdPeriod < pIdPeriod AND
b.cc != 305 AND
(
b.cc = 302 AND(b.tipomov != 4)
OR
b.cc != 302 AND(1=1)-- emular el TRUE
)
AND a.type != 7
GROUP BY 1 HAVING total != 0
) AS ncv;
RETURN ncv;
END;
/
The error is SQL command not properly ended. Sqldeveloper shows "AS ncv" underlined. Is there any problem with group by or having clause ?
ASkeyword. Oracle does not allow this for a table alias.