PLSQL shows error: PLS-00103 in function when using an Exception

Asked

Viewed 35 times

0

PLSQL is reporting error PLS-00103 when I try to compile this function. Note: If I try to run only with the first option of this case "P_TIP = 1" it runs normally. If I try to add the case "P_TIP = 2" it simply returns this error.

I reiterate that the two Selects within the cases are working perfectly in the bank, I just copied them to the function by inserting the INTO P_VALOR.

CREATE OR REPLACE FUNCTION FC_PERS_RAIOX (P_ANO INT, P_CODPARCMATRIZ INT, P_TIP INT)
RETURN FLOAT
IS
   P_VALOR FLOAT;
BEGIN

    BEGIN
        -- BUSCA A VENDA ACUMULADA PARA O CLIENTE
    CASE WHEN P_TIP = 1 THEN

         SELECT
         SUM(MRG.VLRTOT) INTO P_VALOR
         FROM AD_DADOSMRG_EX MRG
         WHERE
         MRG.DTENTSAI BETWEEN '01/01/'||P_ANO AND '31/12/'||P_ANO AND
         MRG.TIPMOV IN ('V', 'D') AND
         MRG.GRUPO IN ('VENDAS') AND
         MRG.CODPARCMATRIZ = P_CODPARCMATRIZ
         GROUP BY 1;
         -- BUSCA DESCONTO MEDIO DO CLIENTE
    CASE WHEN P_TIP = 2 THEN
         
         SELECT
         ROUND((1-(SUM(DECODE(TO_CHAR(MRG.DTENTSAI, 'YYYY'), P_ANO, MRG.VLRTOT,0))/SUM(DECODE(TO_CHAR(MRG.DTENTSAI, 'YYYY'), P_ANO, MRG.PRTAB,0))))*100,2) INTO P_VALOR
         FROM AD_DADOSMRG_EX MRG
         WHERE
         MRG.TIPMOV IN ('V', 'D') AND
         MRG.GRUPO IN ('VENDAS') AND
         MRG.CODPARCMATRIZ = P_CODPARCMATRIZ
         GROUP BY 1;
         END CASE;
              
    EXCEPTION WHEN NO_DATA_FOUND THEN

        P_VALOR := 0;
    END;

    RETURN P_VALOR;

END;

inserir a descrição da imagem aqui

1 answer

0

figured out the problem.

I was creating two CASES.

AT THE LINE WHERE IT IS WRITTEN: CASE WHEN P_TIP = 2 THEN CORRECT WOULD BE ONLY: WHEN P_TIP = 2 THEN

Browser other questions tagged

You are not signed in. Login or sign up in order to post.