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