x

When you issue the adapter SET OPTIFTHENELSE command, the adapter attempts to deliver the construct of a FOCUS IF-THEN-ELSEE DEFINE field to the RDBMS as a CASE expression. The DEFINE field must be an object of a selection test or an aggregation request. The DEFINE definition may be specified in the TABLE request or in the Master File.

ENGINE sqlengine SET OPTIFTHENELSE {ON|OFF}

where:

sqlengine

Indicates the target RDBMS. Valid values are DB2, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

ON

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

OFF

Disables IF-THEN-ELSE optimization. NOCASE and OFF NOCASE are synonyms for OFF.

There is no guarantee that the SQL that is generated will improve performance for all requests. If you find that this feature does not improve performance, set OPTIFTHENELSE OFF to disable the feature.

IF-THEN-ELSE optimization applies to SELECT statements created as a result of FOCUS TABLE requests and is subject to the limitations described in SQL Limitations on Optimization of DEFINE Expressions.

Top of page

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:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
ENGINE DB2 SET OPTIFTHENELSE ON
DEFINE FILE EMPINFO
DEF1 = IF (LAST_NAME EQ ' ') AND (FIRST_NAME EQ '  ')
       AND (DEPARTMENT EQ 'MIS') THEN 1 ELSE 0;
END
 
TABLE FILE EMPINFO
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                                                 
USER1."EMPINFO" T1                                             
 WHERE                                                
((CASE WHEN (((T1."LN" = ' ') AND (T1."FN" = ' ')) AND
(T1."DPT" = 'MIS')) THEN 1 ELSE 0 END) = 1)           
 FOR FETCH ONLY;

Top of page

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:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
ENGINE DB2 SET OPTIFTHENELSE ON
DEFINE FILE EMPINFO
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 EMPINFO
SUM MAX.CURRENT_SALARY 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                                                         
USER1."EMPINFO" T1                                                     
 WHERE                                                        
((CASE (T1."LN") WHEN 'SMITH' THEN 1 WHEN 'JONES' THEN 2 WHEN
'CARTER' THEN 3 ELSE 0 END) = 1)                              
 FOR FETCH ONLY;

Top of page

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:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
ENGINE DB2 SET OPTIFTHENELSE ON                 
DEFINE FILE EMPINFO                           
DEF3 = IF FIRST_NAME EQ 'RITA' THEN 1 ELSE 0;
END                                          
TABLE FILE EMPINFO                            
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                                                
USER1."EMPINFO" T1                                            
 WHERE                                               
((CASE (T1."FN") WHEN 'RITA' THEN 1 ELSE 0 END) = 2)
 FOR FETCH ONLY;

x

Arithmetic expressions return a single number. DEFINE fields are classified as arithmetic if the expression on the right includes one or more of the following elements:

• Real-field operands of numeric data type (I, P, D, F).
• Numeric constants.
• Arithmetic operators (**, *, /, + , -).
• The subtraction of one DATE field from another.
• DEFINE-field operands satisfying any of the preceding items.

For example, the arithmetic expression in the following DEFINE uses a numeric real-field operand (CURR_SAL), a previously-specified DEFINE field (OTIME_SAL), and two numeric constants (1.1 and 100):

NEW_SAL/D12.2= ((CURR_SAL + OTIME_SAL) * 1.1) - 100;

x

Character string expressions return a character string. DEFINE fields are classified as character strings if the expression on the right includes one or more of the following elements:

• Real-field operands of alphanumeric (A) data type.
• String constants.
• String concatenation operators ('|').
• DEFINE-field operands satisfying any of the preceding items.

For example, the character string expression in the following DEFINE uses two alphanumeric field operands (LAST_NAME and FIRST_NAME), a string constant (','), and string concatenation operators (|):

FORMAL_NAME/A36= LAST_NAME | ',' | FIRST_NAME;

x

Logical expressions return one of two values: True (1) or False (0). DEFINE fields are classified as logical if the expression on the right includes any of the following elements:

• Real-field operands of any FOCUS-supported data type (including DATE fields).
• Constants of a data type consistent with fields in the predicate.
• Relational operators (EQ, NE, GT, LT, GE, LE).
• Logical operators (AND, OR, NOT).
• Arithmetic or character string expression operands (described in Valued Expressions).
• DEFINE-field operands satisfying any combination of the preceding items.

For example, the logical expression in the following DEFINE is composed of two expressions connected by the logical operator OR. Each part is itself a logical expression:

SALES_FLAG/I1= (DIV_CODE EQ 'SALES') OR (COMMISSION GT 0);

In the next example, the DEFINE field, QUOTA_CLUB, is the value of a logical expression composed of two other expressions connected by the logical operator AND. Note that the first expression is the previously-specified DEFINE field, SALES_FLAG:

QUOTA_CLUB/I1= (SALES_FLAG) AND (UNITS_SOLD GT 100);