[thelist] [sql] oracle's equiv of COALESCE(...)

Luther, Ron Ron.Luther at compaq.com
Tue Aug 14 16:28:02 CDT 2001


Hi Matt | Jeff,


Oracle's NVL is their 'null value substitution' function.

e.g. NonNullName = NVL(OldName,"This feller ain got no name atall!");

This would substitute the 2nd parameter phrase/value for any null values in
the original field.


I'm guessing you might need to nest a few of these in order to approximate
the same functionality as getting the first non-null in a list:

NewVar = NVL(OldOne,NVL(OldTwo,NVL(OldThree,"Three")));

Which would give you OldOne if OldOne is nonnull, OldTwo if OldOne is null
and OldTwo is not, OldThree if one and two are null and three is not and
"Three" as a default if the whole dang world falls to hell in a handbasket
made from the threads of interwoven old SQl code emails.

Kinda ugly - but maybe approximates the functionality.

RonL.

(Long week - canya tell?)

-----Original Message-----
From: .jeff [mailto:jeff at members.evolt.org]
Subject: RE: [thelist] [sql] oracle's equiv of COALESCE(...)

> From: Warden, Matt
> 
> subject says it all. i'm looking for oracle's equiv
> of COALESCE(), which (at least in sql server) selects
> the first non-null value in a list passed as arguments.

i believe it's NVL()





More information about the thelist mailing list