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

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Chouaebou.

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

Answered by: Chris Saxon - Last updated: February 13, 2020 - 10:04 am UTC

Category: Database Development - Version: 12C

Viewed 100+ 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 we 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

and you rated our response

  (1 rating)

Reviews

February 12, 2020 - 6:02 pm UTC

Reviewer: Mafera from Canada

The if reports me an error. How to resolve this?
Chris Saxon

Followup  

February 13, 2020 - 10:04 am UTC

What error?

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.