if(condition, then, else) in Oracle

Question

Hey all,

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?

Thanks!

Answer 1

You want to use NVL, or NVL2

NVL(t.column, 0)
NVL2( string1, value_if_NOT_null, value_if_null )

Answer 2

Yup, NVL should do the trick.

Answer 3

You want the nvl function: nvl(foo.bar, 0)

Oracle does support various ifs and cases as well.

Answer 4

Use the standard COALESCE function:

SELECT COALESCE(foo.bar, 0) as "bar", ...

Or use Oracle's own NVL function that does the same.

Answer 5

Just do nvl(foo.bar,0), you don't have to do

(select nvl(foo.bar,0)...

Answer 6

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.