Why Use Of Column Alias In Same Select Is Not Supported In Oracle And Mysql?

create table t1 (c1 integer); select c1*3 temp, case when (temp <>3) then 1 else 0 end from t1; Query fails in both Oracle and MySQL But why they doesn't support this type

Solution 1:

An alias can be used in a query select list to give a column a different name. You can use the alias in GROUP BY, ORDER BY, or HAVING clauses to refer to the column:

SELECTSQRT(a*b) AS root FROM tbl_name
  GROUPBY root HAVING root >0;

SELECT id, COUNT(*) AS cnt FROM tbl_name
  GROUPBY id HAVING cnt >0;

SELECT id AS'Customer identity'FROM tbl_name;

Standard SQL disallows references to column aliases in a WHERE clause. This restriction is imposed because when the WHERE clause is evaluated, the column value may not yet have been determined. For example, the following query is illegal:

SELECT id, COUNT(*) AS cnt FROM tbl_name
  WHERE cnt > 0GROUPBY id;

The WHERE clause determines which rows should be included in the GROUP BY clause, but it refers to the alias of a column value that is not known until after the rows have been selected, and grouped by the GROUP BY.

In the select list of a query, a quoted column alias can be specified using identifier or string quoting characters:

SELECT1AS `one`, 2AS'two';

Elsewhere in the statement, quoted references to the alias must use identifier quoting or the reference is treated as a string literal. For example, this statement groups by the values in column id, referenced using the alias a:

SELECT id AS'a', COUNT(*) AS cnt FROM tbl_name
  GROUPBY `a`;

But this statement groups by the literal string 'a' and will not work as expected:

SELECT id AS'a', COUNT(*) AS cnt FROM tbl_name
  GROUPBY'a';

Source: https://docs.oracle.com/cd/E17952_01/refman-5.0-en/problems-with-alias.html

Solution 2:

You can't use aliases in any sections except ORDER BY (in Oracle)

So you can do either:

select c1*3 temp, casewhen (c1*3<>3) then1else0endfrom t1;

Or:

select temp, casewhen (temp <>3) then1else0endfrom (
select c1*3 temp from t1);