Learning > ecsqltutorial > Conditional Expr Edit this page ECSql Conditional Expressions ECSQL supports the following conditional expressions: CASE-WHEN-THEN-ELSE IIF() CASE-WHEN-THEN-ELSE ECSQL supports only searched CASE expressions: CASE WHEN <expr> THEN <expr> [ WHEN <expr> THEN <expr> ...] [ ELSE <expr> ] END Limitations Only primitive type can be used with WHEN, THEN and ELSE. Primitive does not include p2d, p3d, IGometery and NavigationProperties. You can still use sub-queries that return single column and pretty much any SQL expressions. Example -- CASE without ELSE. Returns NULL if the IF case is not met SELECT CASE WHEN Length > 1 THEN 'Big' END FROM test.Foo -- CASE with ELSE. If Length is not greater than 1 then the ELSE expression is returned. SELECT CASE WHEN Length > 1 THEN 'Big' ELSE 'Small' END FROM test.Foo -- Multiple CASE with ELSE SELECT CASE WHEN weekDay=1 THEN 'Mon' WHEN weekDay=2 THEN 'Tue' WHEN weekDay=3 THEN 'Wen' WHEN weekDay=4 THEN 'Thr' WHEN weekDay=5 THEN 'Fri' WHEN weekDay=6 THEN 'Sat' WHEN weekDay=7 THEN 'Sun' ELSE 'Wrong value' END FROM test.Foo IIF ( condition-expr, true-expr , false-expr ) ECSQL supports IIF(), which is really shorthand for CASE WHEN <condition-expr> THEN <true-expr> ELSE <false-expr> END Parameters condition-expr: A condition expression that resolve into a boolean value. e.g. Length > 1.0. true-expr: Value returned when the condition-expr is evaluated to a true value. false-expr: Value returned when the condition-expr is evaluated to a false value. Example -- Returns 'Big' if Length is greater than 1, and 'Small' otherwise SELECT IIF(Length > 1.0, 'Big', 'Small') FROM test.Foo; -- Returns DisplayLabel if Name is NULL, and Name otherwise SELECT IIF(Name IS NULL, DisplayLabel, Name) FROM test.Foo; < Previous | Next > Last Updated: 02 February, 2022