2017  Kodetalk | Feedback | Privacy Policy | Terms | About
userimage

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 = Ythen

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;

userimage

The problem is in the below IF-END IF block:

IF (V_THRESHOLD_MIN_ALERT = Y)

    THEN

      '(Alert Configured)'

    END IF;

    || V_KPI_THRESHOLD_MIN_VALE || Chr(13);

END IF;

You need to assign the value to a variable.

"Y" is a string, and not a boolean value. You must enclose it within single-quotation marks.

For example,

IF (V_THRESHOLD_MIN_ALERT = 'Y')

    THEN

      BODY := BODY||'(Alert Configured)';

ELSE

    BODY := BODY|| V_KPI_THRESHOLD_MIN_VALE || Chr(13);

END IF;

Alternatively, you could use CASE expression:

For example,

BODY := BODY || 'Minimum Threshold' ||

        CASE

        WHEN V_THRESHOLD_MIN_ALERT = 'Y' THEN

           'Alert Configured'

        END || V_KPI_THRESHOLD_MIN_VALE || Chr(13);

Also,

BODY VARCHAR2(4000) := '';

Answer is