Menu
MySQL/MSSQL has a neat little inline if function you can use within queries to detect null values, as shown below.
SELECT
...
foo.a_field AS "a_field",
SELECT if(foo.bar is null, 0, foo.bar) AS "bar",
foo.a_field AS "a_field",
...
The problem I'm running into now is that this code is not safe to run on an Oracle database, as it seems not to support this inline if syntax.
Is there an equivalent in Oracle?
934
IF-THEN-ELSIF Statement. The IF-THEN-ELSIF statement is mainly used where one alternative should be chosen from a set of alternatives, where each alternative has its own conditions to be satisfied.
PL/SQL IF THEN ELSE statementIf the condition evaluates to TRUE, then the statements between THEN and ELSE execute. In case the condition evaluates to FALSE or NULL, the else_statements between ELSE and END IF executes.
An IF statement can have any number of ELSIF clauses; the final ELSE clause is optional. Boolean expressions are evaluated one by one from top to bottom. If any expression returns TRUE , its associated sequence of statements is executed and control passes to the next statement.
Yes, you can.
To supplement the rest of the answers here, which deal primarily with NULL values and COALESCE/NVL/NVL2:
SELECT *
FROM TheTable
WHERE field1 = CASE field2 WHEN 0 THEN 'abc' WHEN 1 THEN 'def' ELSE '' END
CASE statements are not as succinct, obviously, but they are geared towards flexibility. This is particularly useful when your conditions are not based on NULL-ness.
195
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
