Skip to main content

This is documentation for Caché & Ensemble. See the InterSystems IRIS version of this content.

For information on migrating to InterSystems IRIS, see Why Migrate to InterSystems IRIS?

IIF

Returns one of two values, depending on the value of a given logical expression.

Returned Type

This function returns a number or a string, depending on the arguments used and the value of the logical expression.

Syntax and Details

IIF(check_expression, expression1, expression2)
  • expression1 and expression2 are numeric or string expressions. They do not have to be the same type.

    DeepSee MDX does not support other types of arguments.

    To compare to a null value, use the ISNULL function instead.

  • check_expression is a logical expression, typically that compares a measure or a property to a constant.

If check_expression is true, the function returns the value given by expression1. Otherwise, it returns the value given by expression2.

Example

For example:

SELECT IIF(MEASURES.[%COUNT]<500, "fewer than 500", "500 or more") ON 0, diagd.MEMBERS ON 1 FROM patients

                                       IIF
1 None                          500 or more
2 asthma                        500 or more
3 CHD                        fewer than 500
4 diabetes                      500 or more
5 osteoporosis               fewer than 500

As a variation, the following query uses %LABEL to apply a suitable caption to the data column:

SELECT %LABEL(IIF(MEASURES.[%COUNT]<500, "fewer than 500", "500 or more"),"Patient Count") ON 0, 
diagd.MEMBERS ON 1 FROM patients

                             Patient Count
1 None                          500 or more
2 asthma                        500 or more
3 CHD                        fewer than 500
4 diabetes                      500 or more
5 osteoporosis               fewer than 500

For another example, the following query uses the value of a property:

SELECT %LABEL(IIF(homed.h1.CURRENTMEMBER.PROPERTIES("Population")>20000,"big","small"),
"Town Size") ON 0, 
homed.city.MEMBERS ON 1 FROM patients
                                 Town Size
1 Cedar Falls                           big
2 Centerville                           big
3 Cypress                             small
4 Elm Heights                           big
5 Juniper                             small
6 Magnolia                            small
7 Pine                                small
8 Redwood                               big
9 Spruce                              small

The following example examines the name of the member and conditionally suppresses display of a measure:

WITH MEMBER MEASURES.iif AS 'IIF(homed.CURRENTMEMBER.PROPERTIES("name")="Pine"," ",
MEASURES.[%COUNT])' SELECT MEASURES.iif ON 0, homed.city.MEMBERS ON 1 FROM patients
 
                                       iif
1 Cedar Falls                         1,120
2 Centerville                         1,106
3 Cypress                             1,139
4 Elm Heights                         1,078
5 Juniper                             1,109
6 Magnolia                            1,122
7 Pine
8 Redwood                             1,128
9 Spruce                              1,108

See Also

Feedback