Preface
Function reference
- Function components
Constants
Operators
Dates
Functions
- Function overview
  - Aggregate functions
    - Filter conditions
  - Conversion functions
  - Data cleansing functions
  - Date functions
  - Encoding functions
  - Financial functions
  - Horizontal expansion functions
  - Numeric functions
  - Scientific functions
  - Special functions
  - String functions
  - Test functions
  - Window functions
    - Aggregate functions as window functions
    - Nested aggregate functions as window functions
- Function quick reference
- Functions in advanced mode
- %OPR_CONCAT%
- %OPR_CONCATDELIM%
- %OPR_IIF%
- %OPR_SUM%
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- AES_GCM_DECRYPT
- AES_GCM_ENCRYPT
- ASCII
- AVG
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COMPRESS
- CONCAT
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DECODE
- DECOMPRESS
- ENC_BASE64
- ERROR
- EXP
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- IS_DATE
- IS_NUMBER
- IS_SPACES
- ISNULL
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SET_DATE_PART
- SETCOUNTVARIABLE
- SETMAXVARIABLE
- SETMINVARIABLE
- SETVARIABLE
  - Differences between SETVARIABLE, SETMAXVARIABLE, and SETMINVARIABLE
- SHA256
- SIGN
- SIN
- SINH
- SOUNDEX
- SQRT
- STDDEV
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_FLOAT
- TO_INTEGER
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- UUID_STRING
- VARIANCE
System variables
Datatype reference

Function Reference

Back Next

%OPR_IIF%

Uses the IIF function and expands an expression in an expression macro to evaluate a set of IIF statements.

Syntax

%OPR_IIF[ condition, macro_input_field [,value ]]%

The following table describes the arguments for this function:

Argument	Required/ Optional	Description
condition	Required	The condition you want to evaluate. You can enter any valid expression that evaluates to TRUE or FALSE. You can enter a macro input field or an expression that includes at least one macro input field.
macro_input_field	Required	The values you want to return if the condition is TRUE. Any data type except Binary. The return value is the data type specified by this argument. You can enter a macro input field or an expression that includes at least one macro input field.
value	Optional	The value you want to return if the condition is FALSE. Any data type except Binary. In a horizontal or vertical macro, you can enter any valid expression. In a hybrid macro, enter the input macro field that is used to define the output macro field.

Unlike conditional functions in some systems, the FALSE (value
) condition in the %OPR_IIF% function is not required. If you omit value
, the function returns the following values when the condition is FALSE:

Zero if macro_input_field
is a Numeric data type.

Empty string if macro_input_field
is a String data type.

NULL if macro_input_field
is a Date/Time data type.

Return Value

macro_input_field
if the condition is TRUE.

value
if the condition is FALSE.

If the data contains multibyte characters and the condition argument compares string data, the return value depends on the code page of the Secure Agent that runs the task.

%OPR_IIF% and Data types

When you use %OPR_IIF%, the data type of the return value is the same as the data type of the result with the greatest precision.

When at least one result is Double, the data type of the return value is Double.

Example

The following macro input fields %tmin% and %tmax% create ranges that correspond to the following

%type%

macro input field:

%tmin%	%tmax%	%type%
0	30	very cold
31	60	cold
61	90	warm
91	100	hot
101	150	very hot

The following expression assigns a type based on the temperature data:

%OPR_IIF [ (TEMP >= %tmin%) AND (TEMP <= %tmax%), '%type%', 'out of range' ]%

%OPR_IIF% evaluates the following temperatures and returns a type:

TEMP	RETURN VALUE
79	warm
21	very cold
170	out of range
99	hot
90	warm

Functions

Download Guide

Watch

Comments

Cloud Data Integration Homepage

0 COMMENTS

Back Next

We’d like to hear from you! Log in to comment.

Rename Saved Search

Table of Contents

Function Reference

Function Reference

%OPR_IIF%

%OPR_IIF%

Syntax

Return Value

%OPR_IIF% and Data types

Example