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.
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.
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
Data types: https://duckdb.org/docs/sql/data_types/overview
All “intuitive” stuff works as expected, such as
>=, `<=’, ‘<>’, ‘!=’,’=’
IS NOT NULL
parentheses for precedense handling
GATE_NUMBER IN (1, 2, 3, 9)
GATE_NUMBER BETWEEN 4 and 8
See? It is harder to find what is not implemented…