mercredi 12 février 2020

Transform IF to CASE and used the result in the SQL query

FUNCTION F_CALCUL_TAUX (P1 IN NUMBER,P2 IN NUMBER,P3 IN NUMBER,P4 IN NUMBER,P5 IN CHAR,P6 IN NUMBER, P7 IN NUMBER,P8 IN NUMBER,P9 IN NUMBER,P10 IN CHAR) RETURN VARCHAR2 IS 

VAR1      NUMBER(14,2); VRESULT   VARCHAR2(50); 

BEGIN
VAR1 := P3;

IF  ((P4 <= 0 OR P4 IS NULL) AND (P1 > 0)) OR ((P1 <= 0) OR (P1 IS NULL) )    
     THEN  VRESULT := 'Taux1'; 
ELSE
    IF P5 >= '2006' THEN IF P6 > 0 AND P3 IS NULL THEN VAR1 := P6; END IF;  

       IF ( P7 > 0  AND P6 > 0  AND P8 > 0) OR
            ((NVL(P7,0) = 0) AND (P6 > 0) AND (P9 < 15000000 OR P9 IS NULL) AND (UPPER(P10) = 'N')) THEN 
            VRESULT := 'Taux2';
       END IF;
    END IF;

    IF NVL(VAR1,0) = 0 AND NVL(P_MNT_L420B,0) = 0 THEN VRESULT := 'Taux3';
    END IF;  

    IF VAR1 > 0 AND NVL(P_MNT_L420B,0) = 0 THEN VRESULT := 'Taux4';
    END IF;

    IF P5 >= '2004' THEN VRESULT := 'Taux4';
    END IF;

    IF P5 <= '2003' THEN VRESULT := 'Taux5';
    END IF;

END IF;

RETURN VRESULT; 

END;

Aucun commentaire:

Enregistrer un commentaire