Re: Choose one of two values

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Mon, 28 Sep 2009 11:22:00 -0700 (PDT)
Message-ID: <de966b2c-6741-4126-8051-7a3f1752e044_at_b18g2000vbl.googlegroups.com>

On Sep 28, 10:56 am, Sashi <small..._at_gmail.com> wrote:
> On Sep 28, 10:40 am, Sashi <small..._at_gmail.com> wrote:
>
>
>
>
>
> > 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
>
> 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- Hide quoted text -
>
> - Show quoted text -

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 -- Received on Mon Sep 28 2009 - 13:22:00 CDT