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 types can be used with WHEN, THEN and ELSE. This does not include p2d, p3d, IGeometery 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]
Last Updated: 29 April, 2025
Found something wrong, missing, or unclear on this page? Raise an issue in our repo.