Calculated field
IIF([flag], 'ready', 'hold', 'unknown')
Sample data
| label | quantity | amount | flag |
|---|---|---|---|
| cedar cedar | 65 | 12.8 | True |
| ORCHID-west | 66 | -7.6 | False |
| maple-east | 67 | 0 | null |
| willow north | 68 | 25.3 | True |
| birch-birch | 69 | 4.9 | False |
| pine/south | 70 | -1.2 | null |
| ash | 71 | 9 | True |
| null | null | null | null |
The same calculation as SQL
SELECT CASE WHEN flag IS TRUE THEN 'ready' WHEN flag IS FALSE THEN 'hold' ELSE 'unknown' END AS result FROM input
Runs the example above.
SELECT CASE WHEN flag IS TRUE THEN 'ready' WHEN flag IS FALSE THEN 'hold' ELSE 'unknown' END AS result FROM input
Written for this dialect; not run here.
SELECT CASE WHEN flag IS TRUE THEN 'ready' WHEN flag IS FALSE THEN 'hold' ELSE 'unknown' END AS result FROM input
Written for this dialect; not run here.
Result
| result |
|---|
| ready |
| hold |
| unknown |
| ready |
| hold |
| unknown |
| ready |
| unknown |
Same rows from the calculation and from the SQL in DuckDB.
Where a naive translation goes wrong
- Replacing IIF with CASE WHEN flag THEN 'ready' ELSE 'hold' END changes the shown values or null handling.
Naive SQL
SELECT CASE WHEN flag THEN 'ready' ELSE 'hold' END AS result FROM input
result ready hold hold ready hold hold ready hold Matches the calculation
SELECT CASE WHEN flag IS TRUE THEN 'ready' WHEN flag IS FALSE THEN 'hold' ELSE 'unknown' END AS result FROM input
result ready hold unknown ready hold unknown ready unknown
Signature
IIF(boolean, then, else, [unknown])