Optimizing IF-THEN-ELSE Using CASE

How to:

Reference:

Optimization is the process in which the adapter translates the projection, selection, join, sort, and aggregation operations of a report request into their SQL equivalents and passes them to the RDBMS for processing.

In some cases the relational adapters can optimize IF-THEN-ELSE DEFINE expressions as part of aggregation or record selection.

In prior releases, two types of optimization were available for IF-THEN-ELSE DEFINE fields:
Top of page
x
Syntax: How to Control Optimization of IF-THEN-ELSE Expressions
ENGINE SQL SET OPTIFTHENELSE {ON|OFF}

where:

ON

Generates a CASE statement for IF-THEN-ELSE expressions and DECODE expressions whenever possible. ON is the default value. Note that CASE and ON CASE are synonyms for ON.

OFF

Does not optimize IF-THEN-ELSE or DECODE expressions. Note that NOCASE and OFF NOCASE are synonyms for OFF.

Top of page
x
Reference: Usage Notes for SET OPTIFTHENELSE
Example: Using IF-THEN-ELSE Optimization Without Aggregation

Consider the following request that has a WHERE condition on an IF-THEN-ELSE DEFINE field named DEF1:

ENGINE DB2 SET OPTIFTHENELSE ON
DEFINE FILE DB2EMP
DEF1 = IF (LAST_NAME EQ ' ') AND (FIRST_NAME EQ '  ')
       AND (DEPARTMENT EQ 'MIS') THEN 1 ELSE 0;
END
 
TABLE FILE DB2EMP
PRINT DEPARTMENT LAST_NAME FIRST_NAME
WHERE DEF1 EQ 1
END

The adapter generates an SQL request that incorporates the IF-THEN-ELSE condition corresponding to the IF-THEN-ELSE DEFINE field and the WHERE DEF1 EQ 1 test as a CASE statement: 

SELECT                                               
T1."LN",                                              
T1."FN",                                              
T1."DPT"                                              
 FROM                                                 
DB2EMP T1                                             
 WHERE                                                
((CASE WHEN (((T1."LN" = ' ') AND (T1."FN" = ' ')) AND
(T1."DPT" = 'MIS')) THEN 1 ELSE 0 END) = 1)           
 FOR FETCH ONLY;
Example: Using IF-THEN-ELSE Optimization With Aggregation

The following request displays the maximum salary when an IF-THEN-ELSE DEFINE field named DEF2 equals 1:

ENGINE DB2 SET OPTIFTHENELSE ON
DEFINE FILE DB2EMP
DEF2 = IF LAST_NAME EQ 'SMITH' THEN 1 ELSE IF LAST_NAME EQ 'JONES' THEN 2
       ELSE IF LAST_NAME EQ 'CARTER' THEN 3 ELSE 0;
END
TABLE FILE DB2EMP
SUM MAX.CURR_SAL IF DEF2 EQ 1
END

The adapter generates an SQL request that incorporates the aggregation, the IF-THEN-ELSE DEFINE field, and the condition corresponding to the WHERE DEF2 EQ 2 test as a CASE statement:

AGGREGATION DONE ... 
 SELECT                                                       
 MAX(T1."CSAL")                                               
 FROM                                                         
DB2EMP T1                                                     
 WHERE                                                        
((CASE (T1."LN") WHEN 'SMITH' THEN 1 WHEN 'JONES' THEN 2 WHEN
'CARTER' THEN 3 ELSE 0 END) = 1)                              
 FOR FETCH ONLY;
Example: Using IF-THEN-ELSE Optimization With a Condition That Is Always False

The following request has a condition that is always false because the IF-THEN-ELSE DEFINE field named DEF3 is defined to be either 1 or 0, never 2:

ENGINE DB2 SET OPTIFTHENELSE ON                 
DEFINE FILE DB2EMP                           
DEF3 = IF FIRST_NAME EQ 'RITA' THEN 1 ELSE 0;
END                                          
TABLE FILE DB2EMP                            
PRINT FIRST_NAME                             
IF DEF3 EQ 2                                 
END

Because DEF3 EQ 2 will never be true, the adapter passes the CASE statement ((CASE (T1."FN") WHEN 'RITA' THEN 1 ELSE 0 END) = 2), which is always false to the RDBMS, returning zero records from the RDBMS:

 SELECT                                              
T1."FN"                                              
 FROM                                                
DB2EMP T1                                            
 WHERE                                               
((CASE (T1."FN") WHEN 'RITA' THEN 1 ELSE 0 END) = 2)
 FOR FETCH ONLY;
Information Builders