Hi all,

I'm running a simple select stmt thus:

select A.value, b.value
from A, B
where ...

However, either A.value or B.value is null and I need to select the
non null value.
Is there a way of specifying within pl/sql this construct?

Pseudocode:

Select ( if (A.value) is null and (B.value) is not null then B.value
else
if (B.value) is null and (A.value) is not null then
A.value
else
if (B.value) is not null and (A.value) is not null then
A.value)
From A, B
where ...

In the third case above, either is ok so I arbitrarily cose A.value.

Hope this is clean enough.

Thanks in advance,
Sashi

On Sep 28, 10:40*am, Sashi <small... (AT) gmail (DOT) com> wrote:
Well, I found this nifty string function that seems to do what I need:

select coalesce(A.value, B.value)
from A, B
where ...

If there's any caveats or any other reason that I shouldn't use this,
please let me know.
Also any other alternatives that work are appreciated as well.
TIMTOWTDI works in all areas for me.

Thanks,
Sashi

On Sep 28, 10:56*am, Sashi <small... (AT) gmail (DOT) com> wrote:
The coalesce function is an ANSI/ISO standard function of the SQL
lanaguage to return the first non-null value encountered in a list.
Being part of the SQL standard I would recommend its use.

HTH -- Mark D Powell --

On Sep 28, 2:22*pm, Mark D Powell <Mark.Powe... (AT) hp (DOT) com> wrote:
Wasn't that serendipitous!
I was checking out the string functions on psoug.org and accidentally
discovered this function.
Thanks for your input, Mark.
Regards,
Sashi