>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
Précédent
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement