Someone can tell me that there is bad in this function, whenever I put a result it goes to the else and I do not know where to attack
Thank you
Someone can tell me that there is bad in this function, whenever I put a result it goes to the else and I do not know where to attack
Thank you
Apparently your mistake is that you put the BETWEEN from the upper bound to the lower bound. According to the documentation , writing
a BETWEEN x AND y
Is parsed as:
a >= x AND a <= y
And evidently the note can not be greater than 100 AND less than 90. You just have to reverse the limits:
CREATE OR REPLACE FUNCTION public.nota_alum(nota integer)
RETURNS text AS
$$
DECLARE
Sobresaliente constant char :='A';
Notable constant char :='B';
Bien constant char :='C';
Suficiente constant char :='D';
Suspendido constant char :='N';
msg TEXT;
BEGIN
CASE
WHEN (nota BETWEEN 90 AND 100) THEN
msg='el alumne te un nota '||Sobresaliente;
WHEN (nota BETWEEN 70 AND 89) THEN
msg='el alumne te un nota '||Notable;
WHEN (nota BETWEEN 60 AND 69) THEN
msg='el alumne te un nota '||Bien;
WHEN (nota BETWEEN 50 AND 59) THEN
msg='el alumne te un nota '||Suficiente;
WHEN (nota BETWEEN 0 AND 49) THEN
msg='el alumne te un nota '||Suspendido;
ELSE
msg='la nota debe estar entre 0 y 100 ';
END CASE;
RETURN msg;
END
$$ LANGUAGE plpgsql;
In my comment I had told you that you should use IF...ELSEIF...ELSE...END IF;
IF (nota BETWEEN 90 AND 100) THEN
msg='el alumne te un nota '||Sobresaliente;
ELSIF (nota BETWEEN 70 AND 89) THEN
msg='el alumne te un nota '||Sobresaliente;
ELSIF (nota BETWEEN 60 AND 69) THEN
msg='el alumne te un nota '||Sobresaliente;
ELSIF (nota BETWEEN 50 AND 59) THEN
msg='el alumne te un nota '||Sobresaliente;
ELSEIF (nota BETWEEN 0 AND 49) THEN
msg='el alumne te un nota '||Sobresaliente;
ELSE
msg='la nota debe estar entre 0 y 100 ';
END IF;
But I disdain, your syntax works perfect and you just have to invert the ranges.