6.3. Conditional Expressions
CASE
The standard SQL CASE
expression has two forms.The “simple” form searches each value
expression from left to rightuntil it finds one that equals expression
:
- CASE expression
- WHEN value THEN result
- [ WHEN ... ]
- [ ELSE result ]
- END
The result
for the matching value
is returned.If no match is found, the result
from the ELSE
clause isreturned if it exists, otherwise null is returned. Example:
- SELECT a,
- CASE a
- WHEN 1 THEN 'one'
- WHEN 2 THEN 'two'
- ELSE 'many'
- END
The “searched” form evaluates each boolean condition
from leftto right until one is true and returns the matching result
:
- CASE
- WHEN condition THEN result
- [ WHEN ... ]
- [ ELSE result ]
- END
If no conditions are true, the result
from the ELSE
clause isreturned if it exists, otherwise null is returned. Example:
- SELECT a, b,
- CASE
- WHEN a = 1 THEN 'aaa'
- WHEN b = 2 THEN 'bbb'
- ELSE 'ccc'
- END
IF
The IF
function is actually a language constructthat is equivalent to the following CASE
expression:
- CASE WHEN condition THEN true_value [ ELSE false_value ]END
if
(condition, true_value)
Evaluates and returnstruevalue
ifcondition
is true,otherwise null is returned andtrue_value
is not evaluated.
if
(_condition, true_value, false_value)
Evaluates and returnstrue_value
ifcondition
is true,otherwise evaluates and returnsfalse_value
.
COALESCE
coalesce
(value1, value2[, …])
Returns the first non-nullvalue
in the argument list.Like aCASE
expression, arguments are only evaluated if necessary.
NULLIF
nullif
(value1, value2)
Returns null ifvalue1
equalsvalue2
, otherwise returnsvalue1
.
TRY
try
(expression)
Evaluate an expression and handle certain types of errors by returningNULL
.
In cases where it is preferable that queries produce
NULL
or default valuesinstead of failing when corrupt or invalid data is encountered, the TRY
function may be useful. To specify default values, the TRY
function can beused in conjunction with the COALESCE
function.
The following errors are handled by TRY
:
- Division by zero
- Invalid cast or function argument
- Numeric value out of range
Examples
Source table with some invalid data:
- SELECT * FROM shipping;
- origin_state | origin_zip | packages | total_cost
- --------------+------------+----------+------------
- California | 94131 | 25 | 100
- California | P332a | 5 | 72
- California | 94025 | 0 | 155
- New Jersey | 08544 | 225 | 490
- (4 rows)
Query failure without TRY
:
- SELECT CAST(origin_zip AS BIGINT) FROM shipping;
- Query failed: Can not cast 'P332a' to BIGINT
NULL
values with TRY
:
- SELECT TRY(CAST(origin_zip AS BIGINT)) FROM shipping;
- origin_zip
- origin_zip
94131
NULL
94025
08544
(4 rows)
Query failure without TRY
:
- SELECT total_cost / packages AS per_package FROM shipping;
- Query failed: / by zero
Default values with TRY
and COALESCE
:
- SELECT COALESCE(TRY(total_cost / packages), 0) AS per_package FROM shipping;
- per_package
- per_package
4
14
0
19
(4 rows)
原文: https://prestodb.io/docs/current/functions/conditional.html