< Index

Operators

This page describes various SQL operators and expressions available in sqlpp23.

CASE operator

The CASE operator allows for conditional expression evaluation, similar to an if/then/else structure in programming languages. sqlpp23 provides a fluent interface to construct CASE expressions.

Syntax:

The general flow to build a CASE expression is:

::sqlpp::case_when(condition1).then(result1) [.when(condition2).then(result2)]... .else_(else_result)

• It starts with ::sqlpp::case_when(condition).
• Followed by .then(result_expression).
• Optionally, one or more additional .when(condition).then(result_expression) clauses can be chained.
• It must end with an .else_(else_result_expression) clause.

Return Type and Type Compatibility:

• At least one .then() clause or the .else_() clause must be called with an argument different from std::nullopt.
• The data type of the entire CASE expression is determined by the data type of the first non-null argument of a .then() clause (or the else_() clause if the arguments of all .then() clauses are std::nullopt`).
• All subsequent .then() arguments, as well as .else_() argument, must have the same data type (nullability may differ) as this first non-null argument.
• The case expression can be NULL if any of the chosen .then() or .else_() expressions can be NULL.

Examples:

Let's assume we have a table foo with columns id (INTEGER), name (TEXT), and category (INTEGER).

1. Simple CASE expression: Map category id to a string representation.

// Assuming foo.category and relevant string values/columns
const auto category_name = ::sqlpp::case_when(foo.category == 1).then("Category A")
                               .when(foo.category == 2).then("Category B")
                               .else_("Unknown Category");

// Example usage in a SELECT statement:
// for (const auto& row : db(select(foo.name, category_name.as(sqlpp::alias::a)).from(foo)...)) {
//   std::cout << row.name << ": " << row.a;
// }

2. Using std::nullopt If the first then argument is not NULL, its type determines the overall non-optional type. If std::nullopt is used in a subsequent then or in else_, the entire CASE expression becomes nullable.

const auto conditional_name = case_when(foo.category == 1).then(foo.name)
                                  .when(foo.category == 3).then(std::nullopt) // Makes expression potentially NULL
                                  .else_(sqlpp::value("Default Name"));

If the first then argument is NULL, then the type of the expression is determined by the first non-NULL then or else_ argument. The entire CASE expression is nullable.

// `then` argument is NULL. `else_` determines the data type of the expression
const auto another_value = ::sqlpp::case_when(foo.id > 100).then(std::nullopt)
                              .else_(foo.id);

CAST Operator

The SQL CAST function is expressed as

// CAST(expression AS type)
cast(expression, as(type));

// e.g.
cast(tab.some_float, as(sqlpp::ingegral{}));

type is any of

sqlpp::boolean{};
sqlpp::integral{};
sqlpp::unsigned_integral{};
sqlpp::floating_point{};
sqlpp::text{};
sqlpp::blob{};
sqlpp::date{};
sqlpp::timestamp{};
sqlpp::time{};

The return type can be null and corresponds to type, see data types.

The following combinations of data types for expression and type are allowed by the library:

• std::nullopt can be cast to any data type.
• any data type can be cast to itself.
• anything can be cast to an from text.
• numeric data types can be cast to other numeric data types.
• date and timestamp can be cast to timestamp and date.

Also see connector documentation for specific considerations.

< Index