How to make derived column in Oracle and then use it?

Question

How I can make/declare/define a derived column in select query and then use it in where clause?

Answer 1

To define a column in an SQL query, you can use pretty much any SQL operation that returns a single value (including select statements). Here are some examples:

select 'Y' from dual;
select (5 * 3) cal_col from dual;
select (select min(col1) from table 2) calc_col from dual;
select nvl(col1, 'N') has_value from mytable;

From my experience, if you want to use a derived column in a select query, then you must define the column as part of an inner select. Here is an example:

select *
from (
 select (col1 * col2) calc_col
   from mytable
) data
where data.calc_col > 30

Another alternative is use the calculation within the where clause itself:

select (col1 * col2) calc_col
  from mytable t
 where (col1 * col2) > 30

If you are performing a count(*) operation, then you can also leverage the HAVING clause:

select field1, count(*)
  from mytable
having count(*) > 3