Conditional Expressions (CASE)

This guide covers Case, the SQL conditional expression — the per-row equivalent of an if/else if/else chain.

Overview

A case expression produces a different value depending on which of its branches matches the row being evaluated. Velocity supports both standard forms:

Case — the expression itself
WhenClause — one branch of that expression
When — adds a branch
Else — sets the fallback value

The generated SQL is standard on every datasource Velocity supports, so a case expression needs no per-database translation and behaves identically everywhere.

Where it can appear: a case expression is a value, so it can be used anywhere a value is expected — a SELECT clause, a WHERE or HAVING condition, a GROUP BY, an ORDER BY, an argument to a function or aggregate, nested inside another case expression, and as the assigned value of an INSERT or UPDATE via SetFieldAsCase.

The Two Forms

Searched CASE

A searched case evaluates a condition per branch and takes the first that is true. Create it with the constructor that takes only an alias (or none at all) and add branches with When(IFilterItem, IElement):

using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Enums;

Table orders = schema["orders"];

Case sizeBand = new Case("size_band")
    .When(new Criterion<int>(orders["quantity"], ConditionalType.GreaterThan, 100), new Literal<string>("Bulk"))
    .When(new Criterion<int>(orders["quantity"], ConditionalType.GreaterThan, 10), new Literal<string>("Standard"))
    .Else(new Literal<string>("Small"));

Query query = new Query()
    .Select([ orders["order_id"], orders["quantity"], sizeBand ])
    .From(orders);

Generated SQL:

SELECT order_id, quantity,
       CASE WHEN quantity > @p0 THEN @p1
            WHEN quantity > @p2 THEN @p3
            ELSE @p4 END AS size_band
FROM orders

Branches are evaluated in the order they are added and the first match wins, so order them from most specific to least specific. In the example above every Bulk row also satisfies the second condition; it is the ordering that makes it come out as Bulk.

Simple CASE

A simple case compares a single operand against a value per branch. Pass the operand to the constructor and add branches with When(IElement, IElement):

Table students = schema["students"];

Case description = new Case("description", students["grade"])
    .When(new Literal<string>("A"), new Literal<string>("Excellent"))
    .When(new Literal<string>("B"), new Literal<string>("Good"))
    .Else(new Literal<string>("Review"));

Query query = new Query()
    .Select([ students["name"], description ])
    .From(students);

Generated SQL:

SELECT name, CASE grade WHEN @p0 THEN @p1 WHEN @p2 THEN @p3 ELSE @p4 END AS description
FROM students

The operand does not have to be a bare column — a function works just as well:

Case regionCode = new Case("region_code", new Upper(sales["region"]))
    .When(new Literal<string>("NORTH"), new Literal<int>(1))
    .Else(new Literal<int>(9));

The two forms are mutually exclusive. Adding a value branch to a searched case (or a condition branch to a simple case) throws InvalidOperationException rather than silently producing SQL that compares nothing.

Omitting ELSE

Else is optional. Without it, a row matching no branch yields NULL:

Case bulkOnly = new Case("bulk_only")
    .When(new Criterion<int>(orders["quantity"], ConditionalType.GreaterThan, 100), new Literal<string>("Bulk"));

// Rows of 100 or fewer come back as NULL rather than an empty string.

That is not just a default — it is what makes conditional aggregation work.

Comparing Two Columns

A branch condition is an ordinary filter item, so it is not limited to comparing a column against a constant. Criterion<T> of Column compares two columns:

Table names = schema["names"];

Case dominantSex = new Case("dominant_sex")
    .When(new Criterion<Column>(names["male_count"], ConditionalType.GreaterThanOrEqualTo, names["female_count"]),
          new Literal<string>("Male"))
    .Else(new Literal<string>("Female"));

Generated SQL:

CASE WHEN male_count >= female_count THEN @p0 ELSE @p1 END AS dominant_sex

NULL on either side. A comparison involving NULL is unknown, not true, so such a row falls through to the next branch or to ELSE. This is standard three-valued logic and behaves the same on every supported datasource. Where a missing value should count as something specific, say so explicitly with Coalesce:

Case dominantSex = new Case("dominant_sex")
    .When(new Criterion<Expression>(
              new Expression(new Coalesce([ names["male_count"], new Literal<int>(0) ])),
              ConditionalType.GreaterThanOrEqualTo,
              new Expression(new Coalesce([ names["female_count"], new Literal<int>(0) ]))),
          new Literal<string>("Male"))
    .Else(new Literal<string>("Female"));

Compound Conditions

A branch takes any filter item, so a whole Filter works as the condition:

Case priority = new Case("priority")
    .When(new Filter([
              new Criterion<string>(orders["region"], "North"),
              new BooleanItem(BooleanType.And),
              new Criterion<int>(orders["quantity"], ConditionalType.GreaterThanOrEqualTo, 150)
          ]),
          new Literal<string>("High"))
    .Else(new Literal<string>("Normal"));

Composition

Nesting

A case expression can be the result of another case expression's branch:

Case innerGrade = new Case(orders["grade"])
    .When(new Literal<string>("A"), new Literal<string>("Bulk-A"))
    .Else(new Literal<string>("Bulk-Other"));

Case detail = new Case("detail")
    .When(new Criterion<int>(orders["quantity"], ConditionalType.GreaterThan, 100), innerGrade)
    .Else(new Literal<string>("NotBulk"));

Calculated Results

A branch result can be any expression, which is the usual way to guard a division so a zero divisor yields NULL rather than an error:

Case pricePerUnit = new Case("price_per_unit")
    .When(new Criterion<int>(orders["quantity"], ConditionalType.GreaterThan, 0),
          new Expression([
              new Cast(DataType.Double, orders["unit_price"]),
              new ArithmeticOperator(ArithmeticType.Divide),
              orders["quantity"]
          ]));

Generated SQL:

CASE WHEN quantity > @p0 THEN CAST(unit_price AS DOUBLE PRECISION) / quantity END AS price_per_unit

Inside a Function

A case expression is an element, so it can be passed to any function:

Expression bandLength = new Expression("band_length", new Length(sizeBand));

Classifying by the Largest of Several Columns

A common shape is "label this row by whichever of these columns is biggest". Greatest compares values across a row — unlike the MAX aggregate, which compares down a column — and a simple case expression then matches that result back against each candidate:

Table names = schema["names"];

Column[] counts = [ names["white_count"], names["black_count"], names["hispanic_count"] ];
string[] labels = [ "White", "Black", "Hispanic" ];

// Coalesce so a missing count reads as zero. Without it, one NULL voids the whole comparison on
// five of the seven supported datasources — see Datasource Limitations.
List<IElement> guarded = counts
    .Select(c => (IElement)new Coalesce([ c, new Literal<int>(0) ]))
    .ToList();

Case raceCase = new Case(new Greatest(guarded));

// The last label is the ELSE, so only the first n-1 need a branch. Ties break by branch order.
for (int i = 0; i < counts.Length - 1; i++)
{
    raceCase.When(guarded[i], new Literal<string>(labels[i]));
}

raceCase.Else(new Literal<string>(labels[^1]));

Update update = new Update(names);
update.SetFieldAsCase("race", raceCase);

Generated SQL:

UPDATE names
SET race = (CASE GREATEST(COALESCE(white_count, 0), COALESCE(black_count, 0), COALESCE(hispanic_count, 0))
              WHEN COALESCE(white_count, 0) THEN 'White'
              WHEN COALESCE(black_count, 0) THEN 'Black'
              ELSE 'Hispanic' END)

Least is the mirror image, for classifying by the smallest value.

Two caveats, both covered in Datasource Limitations: GREATEST/LEAST handle NULL arguments differently across datasources, which is why the example wraps each column in Coalesce; and SQL Server only gained these functions in version 2022.

Conditional Aggregation

Because an omitted ELSE yields NULL, and because SUM, AVG, MIN, MAX and COUNT all skip NULLs, a case expression inside an aggregate totals only the rows its branch matches:

Table registrations = schema["registrations"];

Case maleOnly = new Case().When(new Criterion<string>(registrations["sex"], "M"), registrations["births"]);
Case femaleOnly = new Case().When(new Criterion<string>(registrations["sex"], "F"), registrations["births"]);

Query query = new Query()
    .Select([
        registrations["year"],
        new Expression("male_births", new Sum(maleOnly)),
        new Expression("female_births", new Sum(femaleOnly))
    ])
    .From(registrations)
    .GroupBy(registrations["year"]);

Generated SQL:

SELECT year,
       SUM(CASE WHEN sex = @p0 THEN births END) AS male_births,
       SUM(CASE WHEN sex = @p1 THEN births END) AS female_births
FROM registrations
GROUP BY year

This is the portable way to express a filtered aggregate. PostgreSQL and SQLite spell the same idea SUM(births) FILTER (WHERE sex = 'M'), but no other supported datasource accepts that syntax — the case form works on all of them.

Grouping, Filtering and Sorting

GROUP BY a computed value

A case expression can be grouped by directly, which buckets rows by a value that is not stored in any column:

Case band = new Case("band")
    .When(new Criterion<int>(orders["quantity"], ConditionalType.GreaterThan, 100), new Literal<string>("Bulk"))
    .Else(new Literal<string>("Small"));

Query query = new Query()
    .Select([ band, new Expression("band_count", new Count()) ])
    .From(orders)
    .GroupBy(band);

Generated SQL:

SELECT CASE WHEN quantity > @p0 THEN @p1 ELSE @p2 END AS band, COUNT(*) AS band_count
FROM orders
GROUP BY CASE WHEN quantity > @p0 THEN @p1 ELSE @p2 END

Velocity repeats the expression in the GROUP BY rather than referring back to the alias, because grouping by an alias is not portable and several datasources reject it.

Note

This form does not run on DB2 or Teradata — use the subquery form below there, which returns identical results. Repeating the expression puts parameter markers in the GROUP BY, and neither database will accept that: they cannot prove the two copies equivalent, because a parameter's value is unknown when the statement is prepared. DB2 reports SQL0119N, Teradata reports 3504. Both group by a case expression happily once the parameters are out of that clause. See also Datasource Limitations.

Grouping by a case expression portably

GroupBy has an overload taking the subquery that produced the column, which groups by a column of a derived table rather than by a repeated expression:

Case band = new Case("band")
    .When(new Criterion<int>(orders["quantity"], ConditionalType.GreaterThan, 100), new Literal<string>("Bulk"))
    .Else(new Literal<string>("Small"));

Query inner = new Query()
    .Select([ orders["order_id"], band ])
    .From(orders);

Query outer = new Query()
    .Select(band, "band", inner)
    .Select(new Expression("band_count", new Count()))
    .From(inner, "x")
    .GroupBy(band, inner);

Generated SQL:

SELECT ("x"."band") AS "band", COUNT(*) AS "band_count"
FROM (
    SELECT order_id, CASE WHEN quantity > @p0 THEN @p1 ELSE @p2 END AS "band"
    FROM orders
) AS "x"
GROUP BY "x"."band"

Only the item's name is taken, so passing the case expression groups by its alias instead of repeating it. This works on all seven datasources and is the form to reach for whenever DB2 or Teradata are in your mix.

The same overload covers an ordinary column of a derived table — grouping by any subquery output column needs it, not only a computed one.

WHERE and HAVING

A case expression is a select item, so it can be the left-hand side of a criterion:

Query query = new Query()
    .Select(orders["order_id"])
    .From(orders)
    .Where(new Criterion<string>(band, "Bulk"));

Generated SQL:

SELECT order_id FROM orders WHERE (CASE WHEN quantity > @p0 THEN @p1 ELSE @p2 END) = @p3

The same applies to HAVING, where a conditional aggregate is often the condition:

Expression gradeAQuantity = new Expression("grade_a_quantity", new Sum(gradeAOnly));

Query query = new Query()
    .Select([ orders["region"], gradeAQuantity ])
    .From(orders)
    .GroupBy(orders["region"])
    .Having(new Criterion<int>(gradeAQuantity, ConditionalType.GreaterThan, 100));

ORDER BY a custom sort order

Sorting by a case expression imposes an order on values that do not sort usefully on their own:

Case sortKey = new Case("sort_key")
    .When(new Criterion<int>(orders["quantity"], ConditionalType.GreaterThan, 100), new Literal<int>(3))
    .When(new Criterion<int>(orders["quantity"], ConditionalType.GreaterThan, 10), new Literal<int>(2))
    .Else(new Literal<int>(1));

Query query = new Query()
    .Select([ orders["order_id"], sortKey ])
    .From(orders)
    .OrderBy(sortKey)
    .OrderBy(orders["order_id"]);

An aliased case expression is ordered by its alias; one without an alias has the expression repeated in the ORDER BY.

CASE in INSERT and UPDATE

SetFieldAsCase makes a case expression the assigned value, so every row is classified by the database in one statement instead of being read into the application and written back one at a time.

Classifying every row in one UPDATE

Table names = schema["names"];

Case dominantSex = new Case()
    .When(new Criterion<Column>(names["male_count"], ConditionalType.GreaterThanOrEqualTo, names["female_count"]),
          new Literal<string>("Male"))
    .Else(new Literal<string>("Female"));

Update update = new Update(names);
update.SetFieldAsCase("sex", dominantSex);

using (Manager manager = new Manager(connection))
{
    int rowsUpdated = manager.UpdateRecords(update);
}

Generated SQL:

UPDATE names SET sex = (CASE WHEN male_count >= female_count THEN @p0 ELSE @p1 END)

Add a WHERE clause as usual to restrict which rows are classified:

Update update = new Update(names, new Criterion<string>(names["country"], "GB"));
update.SetFieldAsCase("sex", dominantSex);

INSERT

The same method works on any Record. In an INSERT ... VALUES there is no row to reference, so the branches compare literals or subqueries:

Record record = new Record(targets);
record.SetFieldInteger("target_id", 100);
record.SetFieldAsCase("label", new Case(new Literal<int>(7))
    .When(new Literal<int>(7), new Literal<string>("Seven"))
    .Else(new Literal<string>("Other")));

manager.AddRecord(record);

For a row-by-row classification during an insert, put the case expression in the SELECT of an INSERT ... SELECT instead — see AddRecords:

Query source = new Query()
    .Select([ orders["order_id"], band, orders["quantity"] ])
    .From(orders);

int inserted = manager.AddRecords(targets,
    [ targets["target_id"], targets["label"], targets["amount"] ], source);

Best Practices

Order branches from most specific to least specific
- The first matching branch wins, so a broad condition placed early will shadow narrower ones after it
Be deliberate about the missing ELSE
- Omitting it yields NULL, which is exactly right for conditional aggregation and wrong for a column that should never be null
Say what a NULL means
- A comparison against NULL is unknown, not false, so such rows fall through to ELSE
- Wrap the operands in Coalesce where a missing value has a defined meaning
Give a case expression an alias when it is selected
- Every select item in a query needs a distinct name, and the alias is what ORDER BY refers back to
Guard division rather than filtering rows out
- CASE WHEN divisor > 0 THEN ... END yields NULL for the bad rows while keeping them in the result
Prefer a case expression over a filtered aggregate
- FILTER (WHERE ...) only exists on PostgreSQL and SQLite; the case form is portable
Persist a classification you query repeatedly
- Classifying once with an UPDATE and indexing the resulting column turns a per-row computation into an indexed lookup

Next Steps

Expressions - Calculated fields and arithmetic
Filtering - Building WHERE clause conditions
Grouping and Aggregation - Aggregates and GROUP BY
Subqueries - Derived tables, and grouping by their columns
DML Operations - Inserting and updating records
Datasource Limitations - Where behaviour differs by database

Table of Contents

Conditional Expressions (CASE)

Overview

The Two Forms

Searched CASE

Simple CASE

Omitting ELSE

Comparing Two Columns

Compound Conditions

Composition

Nesting

Calculated Results

Inside a Function

Classifying by the Largest of Several Columns

Conditional Aggregation

Grouping, Filtering and Sorting

GROUP BY a computed value

Note

Grouping by a case expression portably

WHERE and HAVING

ORDER BY a custom sort order

CASE in INSERT and UPDATE

Classifying every row in one UPDATE

INSERT

Best Practices

Next Steps