Expressions

Use expressions in SELECT, WHERE, ...

Sometimes you need to not only retrieve the values, you might need to adjust them in order to get test result. Just try what you are used to, depending on how far your database’s dialect is far from the standard, majority will work.

CASE WHEN #

Examples of supported expressions (exceprts):

SELECT CASE g.GATE_TYPE
          WHEN 'Jet Bridge Gate' THEN 1000
          WHEN 'Bus Gate' THEN 2000
          ELSE  0
       END
FROM ....

Other standard forms of CASE expressions are also supported.

Casting #

MS Excel data will more than often come with errors. One of problems might be data types, as MS Excel does not enforce them. What if you got text column instead of expected integer data type? Do you want to identify wrong records?

Casting functions will be your best friends in such situations:

SELECT CAST(PLANE_CAPACITY AS VARCHAR) FROM Aero.DIM_PLANES;

SELECT PLANE_CAPACITY::SMALLINT FROM Aero.DIM_PLANES;

SELECT TRY_CAST('two airplanes' AS INTEGER); -- returns NULL

See:

• Data types: https://duckdb.org/docs/sql/data_types/overview

• Casting: https://duckdb.org/docs/sql/expressions/cast

Other #

All “intuitive” stuff works as expected, such as

• logical operators: >, <, >=, `<=’, ‘<>’, ‘!=’,’=’

• AND, OR, NOT, IS NULL, IS NOT NULL

• parentheses for precedense handling

• IN operator: GATE_NUMBER IN (1, 2, 3, 9)

• BETWEEN: GATE_NUMBER BETWEEN 4 and 8

• LIKE

• collations

• subqueries

• …

See? It is harder to find what is not implemented…

Details: https://duckdb.org/docs/sql/expressions/overview