>I want to use a column alias in a where clause like the following:
>select order_id,
> decode(status, 'Held', '1 - Held', 'Shipped', '2 - Shipped) as ordstatus
>from order_header
>where ordstatus = '1 - Held'
>
>I get 'invalid column name' on ordstatus. Any ideas would be appreciated.

Hello,

I don't think you can do this as the compiler is not aware of
the alias as it compiles the SQL. I can see this not working but why not just use:

select order_id,
decode(status, 'Held', '1 - Held', 'Shipped', '2 - Shipped) as ordstatus
from order_header
where status = 'Held';

Although it makes no sense because your never going to have records of type "shipped" because you limit them away in your where clause.


Perhaps this illustrates better the problem...
SQL> create table order_header
2 (order_id number(1),
3 status varchar2(10));

Table created.

SQL> create view xxx as
2 select order_id,
3 decode(status, 'Held', '1 - Held', 'Shipped', '2 - Shipped' ) as ordstatus
4 from order_header;

View created.

SQL> select * from xxx where ordstatus='Held';

no rows selected

SQL> create view xxx as
2 select order_id,
3 decode(status, 'Held', '1 - Held', 'Shipped', '2 - Shipped' ) as ordstatus
4 from order_header
5 where ordstatus='Held';
where ordstatus='Held'
*
ERROR at line 5:
ORA-00904: invalid column name


SQL>

HTH