Class Case

Namespace: YndigoBlue.Velocity.Model

Assembly: YndigoBlue.Velocity.dll

Represents a SQL CASE expression, producing a different value depending on which branch matches.

public class Case : ISelectItem, IFilterItem, IGroupItem, IElement

Inheritance: object

Case

Implements: ISelectItem

IFilterItem

IGroupItem

IElement

Remarks

CASE is the conditional expression of SQL — the equivalent of an if/else if/else chain evaluated per row. Velocity supports both standard forms:

Searched CASE evaluates a condition per branch and takes the first that is true. Create it with the parameterless constructor (or Case(string) to give it a result-set alias) and add branches with When(IFilterItem, IElement):

CASE WHEN quantity > 100 THEN 'Bulk' WHEN quantity > 10 THEN 'Standard' ELSE 'Small' END

Simple CASE compares a single operand against a value per branch. Create it with Case(IElement) (or Case(string, IElement)) and add branches with When(IElement, IElement):

CASE grade WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Good' ELSE 'Review' END

Branches are evaluated top to bottom and the first match wins, so order the branches from most specific to least specific. When no branch matches and no Else(IElement) has been supplied the expression returns NULL — which is deliberately useful with aggregates, since SUM, AVG, MIN, MAX and COUNT all skip NULLs. SUM(CASE WHEN sex = 'M' THEN population END) therefore totals only the male rows, the portable equivalent of a filtered aggregate.

A case expression can be used anywhere a value is expected: in a SELECT clause, inside a WHERE or HAVING condition, in a GROUP BY, as an argument to a function or aggregate, nested inside another case expression, and as the assigned value of an INSERT or UPDATE through SetFieldAsCase(string, Case).

The generated SQL is standard across every database Velocity supports, so no database-specific translation is applied.

Examples

A searched case expression in a SELECT clause:

var schema = manager.LoadSchema("sales");
var orders = schema["orders"];

var 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"));

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

var results = manager.Retrieve(query);

// SQL: SELECT order_id, quantity,
//             CASE WHEN quantity > 100 THEN 'Bulk'
//                  WHEN quantity > 10 THEN 'Standard'
//                  ELSE 'Small' END AS size_band
//      FROM orders

A simple case expression comparing one operand against several values:

var students = schema["students"];

var 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"));

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

// SQL: SELECT name, CASE grade WHEN 'A' THEN 'Excellent' WHEN 'B' THEN 'Good' ELSE 'Review' END AS description
//      FROM students

Comparing two columns — a case expression is not restricted to comparisons against constants:

var names = schema["names"];

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

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

manager.UpdateRecords(update);

// SQL: UPDATE names SET sex = (CASE WHEN male_count >= female_count THEN 'Male' ELSE 'Female' END)

Conditional aggregation — the portable way to total a subset of rows within a grouped query:

var registrations = schema["registrations"];

// A branch with no ELSE yields NULL, and SUM skips NULLs, so each total counts only its own sex.
var maleOnly = new Case().When(new Criterion<string>(registrations["sex"], "M"), registrations["births"]);
var femaleOnly = new Case().When(new Criterion<string>(registrations["sex"], "F"), registrations["births"]);

var 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"]);

// SQL: SELECT year,
//             SUM(CASE WHEN sex = 'M' THEN births END) AS male_births,
//             SUM(CASE WHEN sex = 'F' THEN births END) AS female_births
//      FROM registrations GROUP BY year

Guarding a division so a zero divisor yields NULL rather than an error:

var totals = schema["totals"];

var percentage = new Case("percentage")
    .When(new Criterion<int>(totals["total"], ConditionalType.GreaterThan, 0),
          new Expression([
              new Cast(DataType.Double, totals["part"]),
              new ArithmeticOperator(ArithmeticType.Divide),
              totals["total"],
              new ArithmeticOperator(ArithmeticType.Multiply),
              new Literal<int>(100)
          ]));

// SQL: CASE WHEN total > 0 THEN CAST(part AS DOUBLE PRECISION) / total * 100 END AS percentage

Constructors

Case(): Creates a searched case expression with no result-set alias.

Case(string): Creates a searched case expression with a result-set alias.

Case(string, IElement): Creates a simple case expression with a result-set alias.

Case(IElement): Creates a simple case expression that compares the supplied operand against each branch value.

Properties

DataType: Gets the data type of this select item, which is always Expression.

ElseResult: Gets the value produced when no branch matches, or null when no fallback has been set.

FilterItemType: Gets the filter item type, identifying this as a case expression within a filter.

IsSearched: Gets whether this is a searched case expression (branches carry conditions rather than values).

Name: Gets the alias for this expression in the result set, or an empty string when none was supplied.

Operand: Gets the operand compared against each branch value, or null for a searched case expression.

Precision: Gets the precision of this select item. Always 0, as a case expression has no predetermined precision.

Scale: Gets the scale of this select item. Always 0, as a case expression has no predetermined scale.

Size: Gets the size of this select item. Always 0, as a case expression has no predetermined size.

Whens: Gets the WHEN branches in the order they will be evaluated.

Methods

Else(IElement): Sets the value produced when no branch matches.

When(IElement, IElement): Adds a branch to a simple case expression.