Re: Use DECODE result in WHERE clause?

On Sep 19, 9:22 am, harryajh <harry..._at_yahoo.co.uk> 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?
>
> thanks
>
> harry

Use:

WHERE DECODE(r.cd_status, NULL, p.cd_status, r.cd_status) = 'visible';

Or wrap the entire query in ()'s and query from the result set, at which time you can use your WHERE status = 'visible' syntax:

select x.*
from
(select ...,..., DECODE(r.cd_status, NULL, p.cd_status, r.cd_status) status, ...) x
where x.status = 'visible';

David Fitzjarrell Received on Wed Sep 19 2007 - 09:49:55 CDT