Use DECODE result in WHERE clause?

From: harryajh <harryajh_at_yahoo.co.uk>
Date: Wed, 19 Sep 2007 07:22:42 -0700
Message-ID: <1190211762.726290.81270@57g2000hsv.googlegroups.com>

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 Received on Wed Sep 19 2007 - 09:22:42 CDT