Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Use DECODE result in WHERE clause?

Re: Use DECODE result in WHERE clause?

From: harryajh <harryajh_at_yahoo.co.uk>
Date: Wed, 19 Sep 2007 07:45:59 -0700
Message-ID: <1190213159.356797.132660@g4g2000hsf.googlegroups.com>


On 19 Sep, 15:43, Serge Rielau <srie..._at_ca.ibm.com> wrote:
> harryajh wrote:
> > I have a horrible SQL statement to write!
>
> > I have to use a RIGHT OUTER JOIN to create a result set from 2 tables.
> > I'm using DECODE to select the correct value if one side doesn't exist
> > e.g -
>
> > DECODE(r.cd_status, NULL, p.cd_status, r.cd_status) as status
>
> > the problem is I want to use the "status" value in the WHERE condition
> > e.g
>
> > WHERE status = "visible"
>
> > but ORACLE always want a table field, so I have to use either
> > "r.cd_status" or "p.cd_status" not a generated value!
>
> > Is it possible to use this value? - if not is there another function/
> > way of doing it?
>
> You will need to repeat the DECODE function in the WHERE clause.
> The reason why you cannot refer to an expression in the SELECT list in a
> WHERE clause is because WHERE semantically precedes SELECT.
> Think of:
> SELECT 1/c1 AS X FROM T WHERE c1 IS NOT NULL
>
> You don't want to get X before the WHERE clause has done it's job...
>
> Cheers
> Serge
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

thanks for such a quick answer, I tried this just before I came back to post my findings - works fine!

many thanks Received on Wed Sep 19 2007 - 09:45:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US