Re: Choose one of two values
From: Sashi <smalladi_at_gmail.com>
Date: Mon, 28 Sep 2009 20:03:40 -0700 (PDT)
Message-ID: <08de3c43-3ab8-4fb6-91f9-42249de9e055_at_k17g2000yqb.googlegroups.com>
On Sep 28, 2:22 pm, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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 --
Date: Mon, 28 Sep 2009 20:03:40 -0700 (PDT)
Message-ID: <08de3c43-3ab8-4fb6-91f9-42249de9e055_at_k17g2000yqb.googlegroups.com>
On Sep 28, 2:22 pm, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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 --
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
Received on Mon Sep 28 2009 - 22:03:40 CDT