CODEWITHSUNDEEP
sqloracle-databaseplsqlconditional-operator
ModeEngage
ModeEngage

Reputation: 342

if(condition, then, else) in Oracle

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?

Upvotes: 13

Views: 42972

Answers (6)

David Andres
David Andres

Reputation: 31811

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.

Upvotes: 19

tuinstoel
tuinstoel

Reputation: 7316

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

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

Upvotes: 0

Tony Andrews
Tony Andrews

Reputation: 132710

Use the standard COALESCE function:

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

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

Upvotes: 14

SquareCog
SquareCog

Reputation: 19676

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

Oracle does support various ifs and cases as well.

Upvotes: 0

Eric Petroelje
Eric Petroelje

Reputation: 60559

Yup, NVL should do the trick.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332781

You want to use NVL, or NVL2

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

Upvotes: 12

Related Questions