--- title: "CASE" slug: "dev-database-reference-sql-functions-conditional-and-cast-case" updated: 2026-10-30T18:18:46Z published: 2026-10-30T18:18:46Z canonical: "docs.vastdata.com/dev-database-reference-sql-functions-conditional-and-cast-case" --- > ## Documentation Index > Fetch the complete documentation index at: https://docs.vastdata.com/llms.txt > Use this file to discover all available pages before exploring further. # CASE *Applies to VAST Cluster 5.5.* *[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Conditional and conversion functions](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast) › Choosing a value.* Returns the result of the first condition that holds. The searched form tests conditions one after the other; the simple form compares one value with a list of values. With no `ELSE`, a row that matches nothing gets NULL. ## Syntax ``` CASE WHEN condition THEN result [WHEN condition THEN result ...] [ELSE result] END CASE value WHEN match THEN result [WHEN match THEN result ...] [ELSE result] END ``` | Part | Type | Description | | --- | --- | --- | | `condition` | `BOOLEAN` | Tested in order; the first that's true picks its `result`. | | `value`, `match` | any type that compares | In the simple form, `value` is compared with each `match` with `=`. | | `result` | any | The value returned. All the results must have one type, or types that combine. | **Returns:** the combined type of the results. ## Set up the examples The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_conditional_and_cast_case`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see. This block connects, defines the `run()` and `plan()` helpers, and creates a table of four orders. The quantity is stored as text, as it might arrive from a form, and the last row has no values: ``` import os import re import adbc_driver_manager import adbc_driver_manager.dbapi as dbapi import adbc_driver_vastdb BUCKET = os.environ["VASTDB_BUCKET"] SCHEMA = f'"{BUCKET}/docs_sql_fn_conditional_and_cast_case"' conn = dbapi.connect( driver=adbc_driver_vastdb.get_driver_path(), db_kwargs={ "vast.db.endpoint": os.environ["QE_ENDPOINT"], "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"], "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"], }, autocommit=True, ) def server_message(e): """The server's message in a driver error, without the request ID or the echoed statement.""" text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e)) found = (re.search(r'message: "(.*?)", request_id', text, re.S) or re.search(r"description: (.*)\)", text, re.S)) message = found.group(1) if found else text.strip().splitlines()[-1] message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S) message = re.split(r"\\n|\n", message)[0].replace('\\"', '"') return message.replace(BUCKET, "") def run(sql): """Run one statement. Print the rows it returns, or the error.""" try: with conn.cursor() as cur: cur.execute(sql) rows = cur.fetchall() if cur.description else [] except (adbc_driver_manager.Error, OSError) as e: print(f"{type(e).__name__}: {server_message(e)}") else: print(rows) def plan(sql): """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert.""" with conn.cursor() as cur: cur.execute("EXPLAIN " + sql) steps = cur.fetchall()[0][1] print("\n".join(line for line in steps.splitlines() if "Rows" not in line)) TABLE = f"{SCHEMA}.orders" run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}") run(f"CREATE TABLE IF NOT EXISTS {TABLE} " "(id BIGINT, qty VARCHAR, price DOUBLE, express BOOLEAN, status INTEGER, note VARCHAR)") run(f"INSERT INTO {TABLE} VALUES " "(1, '3', 2.5, true, 0, 'gift'), (2, 'ten', 4.0, false, 1, NULL), " "(3, ' 12 ', 1.5, true, 2, ''), (4, NULL, NULL, NULL, NULL, NULL)") ``` ``` [] [] [(4,)] ``` ## What it does **Searched form.** `CASE WHEN c1 THEN r1 WHEN c2 THEN r2 ELSE r3 END` tests `c1`, then `c2`, and returns the result of the first condition that's true. If none is true, it returns the `ELSE` result, or NULL when there's no `ELSE`. When two conditions are true, the first wins. **Simple form.** `CASE value WHEN m1 THEN r1 ... END` compares `value` with each `m` with `=`. Values of different types are converted first, so `CASE 2.0 WHEN 2 THEN 'eq' END` and `CASE '2' WHEN 2 THEN 'eq' END` both return `'eq'`. **Result type.** The results are combined into one type: | Results | Result type | | --- | --- | | `1` and `2.5` | `DECIMAL(11,1)` | | `1` and `2.5::DOUBLE` | `DOUBLE` | | `1::INTEGER` and `2::BIGINT` | `BIGINT` | | `1` and the text literal `'x'` | `INTEGER` | | `DATE` and `TIMESTAMP` | `TIMESTAMP` | | a `VARCHAR` column and a `BIGINT` column | Error: `Cannot mix values of type VARCHAR and BIGINT in CASE expression - an explicit cast is required` | **Evaluation.** A result is computed only for the rows that reach it. `CASE WHEN TRY_CAST(qty AS INTEGER) IS NOT NULL THEN CAST(qty AS INTEGER) END` doesn't fail on `'ten'`, because the `CAST` runs only where the test passed. ## NULLs and edge cases | Input | Result | | --- | --- | | `CASE WHEN false THEN 1 END` (no `ELSE`, no match) | `NULL` | | `CASE WHEN NULL THEN 1 ELSE 2 END` (a NULL condition counts as false) | `2` | | `CASE NULL WHEN NULL THEN 'match' ELSE 'no match' END` | `'no match'`: `NULL = NULL` isn't true | | `CASE 1 WHEN 1 THEN 'a' WHEN 1 THEN 'b' END` | `'a'`: the first match wins | | `CASE WHEN 1 THEN 'one' END` (a number as condition) | `'one'`: the number converts to true | | `CASE WHEN 'true' THEN 'yes' END` | `'yes'` | | `CASE WHEN 'x' THEN 'yes' END` | Error: `Conversion Error: Could not convert string 'x' to BOOL` | | `CASE WHEN 1 = 1 THEN 1 ELSE [1] END` | Error: `Cannot mix values of type INTEGER[] and INTEGER_LITERAL in CASE expression - an explicit cast is required` | | `CASE WHEN 1 = 0 THEN CAST('x' AS INTEGER) ELSE 0 END` | `0`: the failing branch isn't reached | The same cases, as the cluster returns them. They use the helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-case#set-up-the-examples): ``` run("SELECT CASE WHEN false THEN 1 END, CASE WHEN NULL THEN 1 ELSE 2 END, " "CASE NULL WHEN NULL THEN 'match' ELSE 'no match' END, CASE 1 WHEN 1 THEN 'a' WHEN 1 THEN 'b' END") run("SELECT CASE WHEN 1 THEN 'one' END, CASE WHEN 'true' THEN 'yes' END, " "CASE WHEN 1 = 0 THEN CAST('x' AS INTEGER) ELSE 0 END, CASE 2.0 WHEN 2 THEN 'eq' END, " "CASE '2' WHEN 2 THEN 'eq' END") run("SELECT typeof(CASE WHEN 1 = 1 THEN 1 ELSE 2.5 END), typeof(CASE WHEN 1 = 1 THEN 1 ELSE 2.5::DOUBLE END), " "typeof(CASE WHEN 1 = 1 THEN 1::INTEGER ELSE 2::BIGINT END), typeof(CASE WHEN 1 = 1 THEN 1 ELSE 'x' END), " "CASE WHEN 1 = 1 THEN DATE '2026-10-16' ELSE TIMESTAMP '2026-10-16 12:00:00' END") run("SELECT CASE WHEN 'x' THEN 'yes' END") run("SELECT CASE WHEN 1 = 1 THEN 1 ELSE [1] END") ``` ``` [(None, 2, 'no match', 'a')] [('one', 'yes', 0, 'eq', 'eq')] [('DECIMAL(11,1)', 'DOUBLE', 'BIGINT', 'INTEGER', datetime.datetime(2026, 10, 16, 0, 0))] InternalError: Conversion Error: Could not convert string 'x' to BOOL InternalError: Binder Error: Cannot mix values of type INTEGER[] and INTEGER_LITERAL in CASE expression - an explicit cast is required ``` ## Examples Label each order by price, and name its status code with the simple form. A NULL price falls through to its own `WHEN ... IS NULL` test; in the simple form, a NULL status matches no `WHEN` and gets the `ELSE`: ``` run(f"SELECT id, CASE WHEN price >= 4 THEN 'high' WHEN price IS NULL THEN 'unknown' ELSE 'low' END, " f"CASE status WHEN 0 THEN 'new' WHEN 1 THEN 'paid' WHEN NULL THEN 'never matches' ELSE 'other' END " f"FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 'low', 'new'), (2, 'high', 'paid'), (3, 'low', 'other'), (4, 'unknown', 'other')] ``` Convert the quantity only where it's a number: ``` run(f"SELECT id, CASE WHEN TRY_CAST(qty AS INTEGER) IS NOT NULL THEN CAST(qty AS INTEGER) END " f"FROM {TABLE} ORDER BY id LIMIT 10") ``` ``` [(1, 3), (2, None), (3, 12), (4, None)] ``` Count matching rows inside an aggregate, and sort by a computed key. Order 3 sorts first: ``` run(f"SELECT sum(CASE WHEN express THEN 1 ELSE 0 END), count(CASE WHEN express THEN 1 END) FROM {TABLE}") run(f"SELECT id FROM {TABLE} ORDER BY CASE WHEN id = 3 THEN 0 ELSE id END LIMIT 10") ``` ``` [(Decimal('2'), 2)] [(3,), (1,), (2,), (4,)] ``` ## How it runs in VAST The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). VAST DataBase can evaluate a comparison of a column with a constant, but not a `CASE`, so a `CASE` on a column is always computed by the Query Engine: - **In the `SELECT` list,** the scan reads the columns the `CASE` uses and the Query Engine computes it for each row it returns. - **In `WHERE`,** the scan sends those columns for every row, and a `FILTER` step in the Query Engine computes the `CASE` and keeps the rows that match. No rows are skipped in storage. The plan of a `CASE` used as a condition shows the `FILTER` step above the scan: ``` plan(f"SELECT id FROM {TABLE} WHERE CASE WHEN price > 2 THEN true ELSE false END") ``` ``` ┌───────────────────────────┐ │ PROJECTION │ │ ──────────────────── │ │ #1 │ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ FILTER │ │ ──────────────────── │ │ CASE WHEN ((price > 2.0))│ │ THEN (true) ELSE false END│ │ │ └─────────────┬─────────────┘ ┌─────────────┴─────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ │ │ Projections: │ │ price │ │ id │ │ │ └───────────────────────────┘ ``` ## Make it faster - **Write the condition directly.** `WHERE price > 2` returns the same rows as the `CASE` above and is pushed down to VAST DataBase, so rows that don't match never leave storage. The plan has no `FILTER` step: ``` plan(f"SELECT id FROM {TABLE} WHERE price > 2") ``` ``` ┌───────────────────────────┐ │ VAST_SCAN │ │ ──────────────────── │ │ Function: VAST_SCAN │ │ Projections: id │ │ │ └───────────────────────────┘ ``` - **Put the most common match first.** Conditions are tested in order, and testing stops at the first that's true. ## Limitations and errors - **Results must combine.** A text column and a number column fail with `Binder Error: Cannot mix values of type VARCHAR and BIGINT in CASE expression - an explicit cast is required`. Cast one of them, for example `CAST(id AS VARCHAR)`. - **Conditions must be `BOOLEAN` or convert to it.** The number `1` and the text `'true'` convert; other text, such as `'x'`, fails with `Conversion Error: Could not convert string 'x' to BOOL`. - **`WHEN NULL` never matches in the simple form.** Use the searched form: `CASE WHEN x IS NULL THEN ...`. - **`GROUP BY` isn't available.** `SELECT CASE ... END AS k, count(*) FROM t GROUP BY k` fails with `The operator 'HASH_GROUP_BY' type=9 is currently not supported.` Use `CASE` inside ungrouped aggregates, as in the counting example. - **Missing parts are syntax errors.** A `CASE` without `WHEN`, or without `END`, fails with `Parser Error: syntax error`. ``` run(f"SELECT CASE WHEN id = 1 THEN id ELSE note END FROM {TABLE} ORDER BY id LIMIT 10") run(f"SELECT id, CASE WHEN id = 1 THEN CAST(id AS VARCHAR) ELSE note END FROM {TABLE} ORDER BY id LIMIT 10") run(f"SELECT CASE WHEN price > 2 THEN 'high' ELSE 'low' END AS k, count(*) FROM {TABLE} GROUP BY k " f"ORDER BY k LIMIT 10") ``` ``` InternalError: Binder Error: Cannot mix values of type VARCHAR and BIGINT in CASE expression - an explicit cast is required [(1, '1'), (2, None), (3, ''), (4, None)] InternalError: The operator 'HASH_GROUP_BY' type=9 is currently not supported. ``` ## Similar functions | Function | Use it for | Example | Result | | --- | --- | --- | --- | | [if](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-if) | a `CASE` with one condition | `if(2 > 1, 'x', 'y')` | `'x'` | | [coalesce](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-coalesce) | the first argument that isn't NULL | `coalesce(NULL, 'b')` | `'b'` | | [nullif](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-nullif) | NULL when two values are equal | `nullif('a', 'a')` | `NULL` | | [greatest](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-greatest), [least](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast-least) | the largest or smallest argument | `greatest(2, 1)`, `least(2, 1)` | `2`, `1` | ``` run("SELECT CASE WHEN 2 > 1 THEN 'x' ELSE 'y' END, if(2 > 1, 'x', 'y'), coalesce(NULL, 'b'), " "nullif('a', 'a'), greatest(2, 1), least(2, 1)") ``` ``` [('x', 'x', 'b', None, 2, 1)] ``` Clean up: ``` run(f"DROP TABLE IF EXISTS {TABLE}") run(f"DROP SCHEMA IF EXISTS {SCHEMA}") conn.close() ``` ``` [] [] ``` ## See also - [Conditional and conversion functions](/develop/docs/dev-database-reference-sql-functions-conditional-and-cast): all the conditional and conversion functions. - [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns). - [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.