I need to use PL-SQL or SQL to do my query.
First: I want to transform all the IF..END IF used in the body of function F_CALCUL_TAUX to CASE..WHEN..END, and use the result inside my query.
Secundo: It's possible to transform that and use it inside the PL-SQL bloc ?
Thanks.
It's mostly a matter of changing IF -> CASE. Provided your using this as a case statement in PL/SQL that's about it.
But...
Calling functions in SQL - especially in the where/join clauses - can be very slow.
Is this why you want to convert this? So you can use the case expression directly in your query?
If so, you'll need to remove the "VRESULT :=" assignments, semi-colons, and ENDs.
Then swap the variable names for the column. e.g.:
LEFT OUTER JOIN TABLE_4 TABLE_4
ON TABLE_4.Taux = CASE
WHEN ((P4 <= 0 OR P4 IS NULL) AND (P1 > 0)) OR ((P1 <= 0) OR (P1 IS NULL) ) THEN
'Taux1'
ELSE
CASE
WHEN P5 >= '2006' THEN IF P6 > 0 AND P3 IS NULL THEN
WHEN ( 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
'Taux2'
WHEN NVL(VAR1,0) = 0 AND NVL(P_MNT_L420B,0) = 0 THEN
'Taux3'
WHEN VAR1 > 0 AND NVL(P_MNT_L420B,0) = 0 THEN
'Taux4'
WHEN ...
END
END