where from a column that does not exist in the table-CodePudding

I have a sql query, I need to do a comparison where of a column that I am renaming based on certain conditions, but I get an error that the column does not exist, how can I do this?

select e.emp_nombre, nvl(e.emp_activo,1) as nivel
from gen_empresa e
where nivel = 1;

CodePudding user response：

You can't use the column alias in the WHERE clause, because WHERE clause is evaluated before the column alias is created.

Either use the same nvl(e.emp_activo,1) expression in the WHERE clause:

select e.emp_nombre, nvl(e.emp_activo,1) as nivel
from gen_empresa e
where nvl(e.emp_activo,1) = 1;

Or use a derived table:

select emp_nombre, nivel
from
(
    select e.emp_nombre, nvl(e.emp_activo,1) as nivel
    from gen_empresa e
)
where nivel = 1;

CodePudding user response：

You can also write your query as CTE:

WITH TMP AS (select e.emp_nombre, nvl(e.emp_activo,1) as nivel
from gen_empresa e)
Select * from TMP where nivel=1;