Re: Use DECODE result in WHERE clause?

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