Oracle: Can You Assign an Alias to the from Clause

oracle: can you assign an alias to the from clause?

Yes, Oracle supports table aliases. It supports AS in the SELECT list but not in the FROM list:

SELECT a.col - b.col AS markup
  FROM RETAIL a,
       COST b
 WHERE b.id = a.id

Most databases support omitting the AS keyword.

That said, table aliases aren't column aliases -- you still need to reference a specific column in the respective table in the SELECT clause, like you see in my update of your example. I also added the WHERE criteria so the query wouldn't be returning a Cartesian product.

Table aliases are sometimes required for derived tables/inline views (AKA subquery, though I find the terminology very vague):

SELECT x.col
  FROM (SELECT t.col,
               MAX(t.date)
          FROM TABLE t
      GROUP BY t.col) x

Here's your query:

Your problem was you were putting the table alias inside the derived table, when it needs to be outside the brackets/parenthesis:

SELECT DISTINCT TO_CHAR(MONTHS_BETWEEN(x.pubdate, y.pubdate), '99.99') AS "Answer"
 FROM (SELECT DISTINCT a.pubdate FROM BOOKS3 a WHERE a.pubid = 2) x,
      (SELECT DISTINCT b.pubdate FROM BOOKS3 b WHERE b.pubid = 4) y

The reason you need the distinct is because of the Cartesian product.

SQL not recognizing column alias in where clause

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.

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.

So, the following query is illegal:

SQL> SELECT empno AS employee, deptno AS department, sal AS salary
  2  FROM emp
  3  WHERE employee = 7369;
WHERE employee = 7369
      *
ERROR at line 3:
ORA-00904: "EMPLOYEE": invalid identifier

SQL>

The column alias is allowed in:

• GROUP BY
• ORDER BY
• HAVING

You could refer to the column alias in WHERE clause in the following cases:

1. Sub-query
2. Common Table Expression(CTE)

For example,

SQL> SELECT * FROM
  2  (
  3  SELECT empno AS employee, deptno AS department, sal AS salary
  4  FROM emp
  5  )
  6  WHERE employee = 7369;

  EMPLOYEE DEPARTMENT     SALARY
---------- ---------- ----------
      7369         20        800

SQL> WITH DATA AS(
  2  SELECT empno AS employee, deptno AS department, sal AS salary
  3  FROM emp
  4  )
  5  SELECT * FROM DATA
  6  WHERE employee = 7369;

  EMPLOYEE DEPARTMENT     SALARY
---------- ---------- ----------
      7369         20        800

SQL>

Using Alias In When Portion of a Case Statement in Oracle SQL

No, you can't refer to the alias elsewhere in the same level of select, other than in the order by clause, because of when Oracle assigns it internally.

From the documentation (emphasis added):

You can use a column alias, c_alias, to label the immediately
preceding expression in the select list so that the column is
displayed with a new heading. The alias effectively renames the select
list item for the duration of the query. The alias can be used in the
ORDER BY clause, but not other clauses in the query.

You would need to use an inner query, something like:

select "Id",
    case "Id"
        when 3
        then 'foo'
        else 'bar'
    end AS "Results"
from (
    select TABLEA.SomeIDNumber AS "Id",
    from TABLEA
);

Using an Alias in a WHERE clause

This is not possible directly, because chronologically, WHERE happens before SELECT, which always is the last step in the execution chain.

You can do a sub-select and filter on it:

SELECT * FROM
(
  SELECT A.identifier
    , A.name
    , TO_NUMBER(DECODE( A.month_no
      , 1, 200803 
      , 2, 200804 
      , 3, 200805 
      , 4, 200806 
      , 5, 200807 
      , 6, 200808 
      , 7, 200809 
      , 8, 200810 
      , 9, 200811 
      , 10, 200812 
      , 11, 200701 
      , 12, 200702
      , NULL)) as MONTH_NO
    , TO_NUMBER(TO_CHAR(B.last_update_date, 'YYYYMM')) as UPD_DATE
  FROM table_a A
    , table_b B
  WHERE A.identifier = B.identifier
) AS inner_table
WHERE 
  MONTH_NO > UPD_DATE

Interesting bit of info moved up from the comments:

There should be no performance hit.
Oracle does not need to materialize
inner queries before applying outer
conditions -- Oracle will consider
transforming this query internally and
push the predicate down into the inner
query and will do so if it is cost
effective. – Justin Cave

Is the 'as' keyword required in Oracle to define an alias?

According to the select_list Oracle select documentation the AS is optional.

As a personal note I think it is easier to read with the AS

How to use the 'as' keyword to alias a table in Oracle?

You can use AS for table aliasing on many SQL servers (at least MsSQL, MySQL, PostrgreSQL) but it's always optional and on Oracle it's illegal.

So remove the AS :

SELECT G.Guest_ID, G.First_Name, G.Last_Name
FROM Guest G

Group by alias (Oracle)

select
  count(count_col),
  alias_column
from
  (
  select 
    count_col, 
    (select value from....) as alias_column 
  from 
    table
  ) as inline
group by 
  alias_column

Grouping normally works if you repeat the respective expression in the GROUP BY clause. Just mentioning an alias is not possible, because the SELECT step is the last step to happen the execution of a query, grouping happens earlier, when alias names are not yet defined.

To GROUP BY the result of a sub-query, you will have to take a little detour and use an nested query, as indicated above.

Use an Alias in Where Clause Subquery in Oracle

You can use an identifier defined in an external query in only one level deep queries. You have to rethink your strategy. My suggestion is to remove the subquery from the select list and put it in the FROM clause. And add another rownumber column like this:

(SELECT 
    ID_PEGAWAI,
    CONCAT(TO_CHAR(abs(sysdate - TO_DATE(TMT_JABATAN))/360,'9,999,999.9'),' TAHUN') MASA_KERJA, 
    ROW_NUMBER() OVER (PARTITION BY ID_PEGAWAI ORDER BY TMT_JABATAN DESC) rownumber
FROM SIMPEG_JABATAN) xxx

And join like:

ON ANGGARAN.SIMPEG_PEGAWAI = xxx.ID_PEGAWAI

Then in the where clause you can do simply:

WHERE
     ....
     AND xxx.rownumber = 1

Complete query:

SELECT
  ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI AS KODE,
  ANGGARAN.SIMPEG_PEGAWAI.NAMA,
  ANGGARAN.SIMPEG_PEGAWAI.NIP,
  ANGGARAN.SIMPEG_ESELON_JABATAN.JABATAN,
  ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.GOLONGAN,
  ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.PANGKAT
FROM 
  ANGGARAN.SIMPEG_PEGAWAI
    INNER JOIN ANGGARAN.SIMPEG_ESELON_JABATAN 
      ON ANGGARAN.SIMPEG_PEGAWAI.ESELON_JABATAN = ANGGARAN.SIMPEG_ESELON_JABATAN.ID_ESELON_JABATAN
    INNER JOIN ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT 
      ON ANGGARAN.SIMPEG_PEGAWAI.PANGKAT = ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.ID_GOLONGAN_PANGKAT
    INNER JOIN (
      SELECT 
        ID_PEGAWAI,
        CONCAT(TO_CHAR(abs(sysdate - TO_DATE(TMT_JABATAN))/360,'9,999,999.9'),' TAHUN') MASA_KERJA, 
        ROW_NUMBER() OVER (PARTITION BY ID_PEGAWAI ORDER BY TMT_JABATAN DESC) rownumber
      FROM SIMPEG_JABATAN
    ) xxx
      ON ANGGARAN.SIMPEG_PEGAWAI.ID_PEGAWAI = xxx.ID_PEGAWAI
WHERE
  ANGGARAN.SIMPEG_PEGAWAI.ST_AKTIF = 1 AND
  ANGGARAN.SIMPEG_PEGAWAI.ESELON2 <> 1 AND
  ANGGARAN.SIMPEG_PEGAWAI.PANGKAT >= 12 AND 
  ANGGARAN.SIMPEG_ESELON_JABATAN.STATUS = 1 AND
  ANGGARAN.SIMPEG_ESELON_JABATAN.ID_ESELON2=2 AND
  xxx.rownumber = 1
ORDER BY ANGGARAN.SIMPEG_KODE_GOLONGAN_PANGKAT.SORT DESC

---

Related Topics