CASE

Applies to VAST Cluster 5.5.

Functions › Conditional and conversion functions › 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

Returns: the combined type of the results.

Set up the examples

The examples connect with the ADBC driver, as in VAST Query Engine (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), 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, "<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:

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

The same cases, as the cluster returns them. They use the helpers from 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

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: all the conditional and conversion functions.
• Where filters run and pushdowns.
• EXPLAIN: how to read a plan.