mercredi 30 septembre 2015

PLS-00103: Encountered the symbol "IF" when expecting one of the following in oracle function

I am writing the below function in which i am getting error as PLS-00103: Encountered the symbol "IF" when expecting one of the following: ( - + case mod new null <an identifier>. I know this error is coming at the line where i am trying to append if condition with THRESHOLD_MIN_ALERT string . I want to append the string Minimum Threshold with the if condition and value. So for example for Minimum threshold it should come like this if V_THRESHOLD_MIN_ALERT = Y then

THRESHOLD_MIN_ALERT(Alert Configured) 100

If the V_THRESHOLD_MIN_ALERT = N then

THRESHOLD_MIN_ALERT 100

If i remove the if condition then my function runs fine.I dont know the syntax of how to append the if codition.Here is my function:

FUNCTION BUILD_ALERT_EMAIL_BODY
    (
      IN_ALERT_LOGS_TIMESTAMP IN TIMESTAMP
    ) RETURN VARCHAR2 AS
    BODY VARCHAR2(4000) := '';
    V_KPI_DEF_ID NUMBER := '';
    V_KPI_TYPE_ID NUMBER := '';
    V_KPI_THRESHOLD_MIN_VALE NUMBER := '';
    V_KPI_THRESHOLD_MAX_VALE NUMBER := '';
    V_THRESHOLD_MIN_ALERT NUMBER;        

    BEGIN

    Select KPI_DEF_ID INTO V_KPI_DEF_ID FROM KPI_LOGS WHERE KPI_LOG_ID = IN_KPI_LOG_ID;
    Select KT.KPI_TYPE_ID INTO V_KPI_TYPE_ID FROM KPI_DEFINITION KD JOIN KPI_TYPE KT ON KD.KPI_TYPE = KT.KPI_TYPE_ID WHERE KD.KPI_DEF_ID = V_KPI_DEF_ID;
    Select THRESHOLD_MAX_VAL INTO V_KPI_THRESHOLD_MAX_VALE FROM KPI_DEFINITION WHERE KPI_DEF_ID = V_KPI_DEF_ID;
    Select THRESHOLD_MIN_VAL INTO V_KPI_THRESHOLD_MIN_VALE FROM KPI_DEFINITION WHERE KPI_DEF_ID = V_KPI_DEF_ID;
    Select THRESHOLD_MIN_ALERT INTO V_THRESHOLD_MIN_ALERT FROM KPI_DEFINITION WHERE KPI_DEF_ID = V_KPI_DEF_ID;
    Select THRESHOLD_Max_ALERT INTO V_THRESHOLD_MAX_ALERT FROM KPI_DEFINITION WHERE KPI_DEF_ID = V_KPI_DEF_ID;

        BODY := 'ALERT TIMESTAMP : ' || to_char(IN_ALERT_LOGS_TIMESTAMP,'DD.MM.YYYY HH24:MI') || Chr(13) || Chr(10);

        IF ((V_KPI_TYPE_ID = 18) OR (V_KPI_TYPE_ID = 19))  THEN  
        BODY := BODY || 'Minimum Threshold' || if (V_THRESHOLD_MIN_ALERT = Y) then '(Alert Configured)' END IF; || V_KPI_THRESHOLD_MIN_VALE || Chr(13);            
        END IF;

       RETURN BODY;
    END BUILD_ALERT_EMAIL_BODY;

Aucun commentaire:

Enregistrer un commentaire