Skip to Main Content
  • Questions
  • Transform IF ELSE END IF TO CASE WHEN

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Chouaebou.

Asked: February 12, 2020 - 1:43 pm UTC

Last updated: February 13, 2020 - 10:04 am UTC

Version: 12C

Viewed 1000+ times

You Asked

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.

with LiveSQL Test Case:

and Chris said...

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

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Mafera, February 12, 2020 - 6:02 pm UTC

The if reports me an error. How to resolve this?
Chris Saxon
February 13, 2020 - 10:04 am UTC

What error?

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.