Introduction
Dans la suite de cet article j'utiliserai les termes suivants :
          - SELECT : ordre SELECT dans son ensemble avec les clauses WITH, SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

          - Clause SELECT ou sous-clause SELECT : partie SELECT proprement dite du SELECT, soit la partie avant le FROM


Sous Oracle il est possible de définir des alias de colonnes dans la clause SELECT mais aussi des alias de tables ou vues dans la clause FROM.
Les alias de la clause FROM sont connus de toutes les autres clauses du SELECT mais ceux de la clause SELECT ne sont connus que dans le ORDER BY. Cette bizarrerie s'explique de la façon suivante : SQL veut dire Structured Query Language, soit Langage de Requête Structurée. C'est la requête qui est structurée, ce qui signifie qu'on ne peut pas écrire un SELECT n'importe comment mais seulement en respectant un ordre bien précis pour les clauses. Cet ordre d'écriture est le suivant

          WITH
          SELECT
          FROM
          WHERE
          GROUP BY / HAVING (GROUP BY et HAVING peuvent interchanger leur place même si HAVING est généralement mis après GROUP BY)
          ORDER BY

Nous voyons bien que le SELECT est en deuxième position, donc les alias de la clause SELECT devraient être connus des clauses suivantes MAIS, et c'est là le piège, Oracle n'analyse pas les ordres SQL dans cet ordre là mais dans un autre ordre. En effet, la première chose que cherche Oracle c'est "Où sont les données" lors du parsing donc il lit le FROM avant toute autre clause, ce qui explique que les alias de tables ou de vues du FROM soient utilisables partout. L'ordre d'analyse d'un Select par Oracle est le suivant :
          FROM
          WHERE
          GROUP BY / HAVING
          SELECT
          ORDER BY
Je n'ai pas mis volontairement la clause WITH car la doc sur Internet n'est pas claire sur quand cette clause est analysée. MAIS cela ne m'empêchera pas de tester cette clause et de montrer qu'elle l'est avant le SELECT!

L'objet de cet article est de valider que la clause SELECT est bien analysée en avant dernière position, juste avant le ORDER BY. Il est dommage que ces alias ne soient pas connus dans tout l'ordre SELECT car cela rendrait les ordres SQL plus lisibles.

Oui, je sais, je n'explique pas pourquoi un alias dans la clause SELECT n'est pas réutilisable dans cette même clause SELECT :-)



Points d'attention

Les alias sous Oracle s'utilisent avec ou sans guillemets s'ils n'ont pas d'espace dans leur nom mais obligatoirement avec des guillemets s'ils ont des espaces dans leur nom.



 

Base de tests

Comme base de tests j'utilise celle d'Oracle, à savoir la base avec les tables EMP et DEPT.
          SQL> desc EMP
          Name              Null?           Type
          ----------------------------------------- -------- -----------------
          EMPNO           NOT NULL           NUMBER(4)
          ENAME                                      VARCHAR2(10)
          JOB                                           VARCHAR2(9)
          MGR                                          NUMBER(4)
          HIREDATE                                  DATE
          SAL                                            NUMBER(7,2)
          COMM                                        NUMBER(7,2)
          DEPTNO                                     NUMBER(2)


          SQL> desc DEPT
          Name                Null?                   Type
          ----------------------------------------- -------- ----------------------------
          DEPTNO           NOT NULL           NUMBER(2)
          DNAME                                       VARCHAR2(14)
          LOC                                            VARCHAR2(13)




Exemples

Nous allons tester chaque clause une par une, dans l'ordre d'écriture, y compris le FROM! Car oui, dans le FROM on peut utiliser des noms de colonnes autres que celles de la jointure grâce ou à cause de la syntaxe ANSI 92.


============================================================================================
Un alias défini dans la clause SELECT est inconnu de la clause WITH.
============================================================================================
Etape 1 : définition de la clause WITH.

          SQL> WITH TESTW AS (SELECT COUNT(*) AS NB FROM EMP WHERE ENAME = 'SMITH') SELECT ENAME AS NOM, (SELECT NB FROM TESTW) FROM EMP ORDER BY ENAME;

          NOM           (SELECTNBFROMTESTW)
          ------------------------------------------------------------
          ADAMS          1
          ALLEN            1
          BLAKE           1
          CLARK           1
          FORD             1
          JAMES           1
          JONES           1
          KING              1
          MARTIN          1
          MILLER          1
          SCOTT           1
          SMITH            1
          TURNER         1
          WARD            1
14 rows selected.


Exemple où l'alias dans un SELECT est inconnu du WITH.
L'alias NOM sur la colonne ENAME est inconnu de la clause WITH.
          SQL> WITH TESTW AS (SELECT COUNT(*) AS NB FROM EMP WHERE NOM = 'SMITH') SELECT ENAME AS NOM, (SELECT NB FROM TESTW) FROM EMP ORDER BY ENAME;

          WITH TESTW AS (SELECT COUNT(*) AS NB FROM EMP WHERE NOM = 'SMITH') SELECT ENAME AS NOM, (SELECT NB FROM TESTW) FROM EMP ORDER BY ENAME
                                                                                                              *
          ERROR at line 1:
          ORA-00904: "NOM": invalid identifier


============================================================================================
Un alias défini dans la clause SELECT n'est pas réutilisable dans cette même clause SELECT.
============================================================================================

Définition OK de l'alias SALAIRE sur la colonne SAL.
          SQL> SELECT SAL AS "SALAIRE", SAL*12 FROM EMP ORDER BY SAL DESC;
          SALAIRE    SAL*12
          -----------------------------------
          5000           60000
          3000           36000
          3000           36000
          2975           35700
          2850           34200
          2450           29400
          1600           19200
          1500           18000
          1300           15600
          1250           15000
          1250           15000
          1100           13200
          950             11400
          800              9600


Impossible de réutiliser l'alias SALAIRE dans la clause SELECT, qu'on mette ou non des guillemets (je précise cela car, pour ceux qui savent ... les guillemets ont de grand pouvoirs sous Oracle mais c'est une autre histoire).
          SQL> SELECT SAL AS SALAIRE, SALAIRE*12 FROM EMP ORDER BY SAL DESC;
          SELECT SAL AS SALAIRE, SALAIRE*12 FROM EMP ORDER BY SAL DESC
                                                    *
          ERROR at line 1:
          ORA-00904: "SALAIRE": invalid identifier


          SQL> SELECT SAL AS "SALAIRE", SALAIRE*12 FROM EMP ORDER BY SAL DESC;
          SELECT SAL AS "SALAIRE", SALAIRE*12 FROM EMP ORDER BY SAL DESC
                                                       *
          ERROR at line 1:
          ORA-00904: "SALAIRE": invalid identifier


          SQL> SELECT SAL AS "SALAIRE", "SALAIRE"*12 FROM EMP ORDER BY SAL DESC;

          SELECT SAL AS "SALAIRE", "SALAIRE"*12 FROM EMP ORDER BY SAL DESC
                                                       *
          ERROR at line 1:
          ORA-00904: "SALAIRE": invalid identifier

          SQL> SELECT SAL AS SALAIRE, "SALAIRE"*12 FROM EMP ORDER BY SAL DESC;
          SELECT SAL AS SALAIRE, "SALAIRE"*12 FROM EMP ORDER BY SAL DESC
                                                    *
          ERROR at line 1:
          ORA-00904: "SALAIRE": invalid identifier


============================================================================================
Un alias défini dans la clause SELECT est inconnu de la clause FROM.
============================================================================================

Avec la syntaxe ANSI 92, il est possible de déplacer dans la clause FROM des restriction situées normalement dans le WHERE. Et, de la sorte, on pourra tester si les alias du SELECT, inconnus dans le WHERE, pourraient être connus dans le FROM.
Attention : il faut utiliser la syntaxe ON et pas USING ni NATURAL JOIN.

Exemple d'une clause WHERE remontée dans le FROM.
Etape 1 : pas de restriction; les employés du département 10 sont sélectionnés.
          SQL> SELECT ENAME, EMP.DEPTNO AS "DEPARTEMENT" FROM EMP JOIN DEPT ON (EMP.DEPTNO = DEPT.DEPTNO);
          ENAME      DEPARTEMENT

          ------------------------------------------------
          CLARK           10
          KING              10
          MILLER          10
          SMITH            20
          JONES           20
          SCOTT           20
          ADAMS          20
          FORD             20
          ALLEN           30
          WARD            30
          MARTIN          30
          BLAKE           30
          TURNER         30
          JAMES           30

Etape 2 : mise en place d'un filtre dans le FROM pour filtrer les employés du département 10.
          SQL> SELECT ENAME, EMP.DEPTNO AS "ID_DEPT" FROM EMP JOIN DEPT ON (EMP.DEPTNO = DEPT.DEPTNO AND EMP.DEPTNO >= 20);
          ENAME           ID_DEPT

          ------------------------------------
          SMITH                     20
          JONES                    20
          SCOTT                     20
          ADAMS                   20
          FORD                      20
          ALLEN                     30
          WARD                     30
          MARTIN                   30
          BLAKE                    30
          TURNER                  30
          JAMES                    30         
          11 rows selected.


Exemple où l'alias DEPARTEMENT dans la clause SELECT est inconnu du FROM.
          SQL> SELECT ENAME, EMP.DEPTNO AS "DEPARTEMENT" FROM EMP JOIN DEPT ON (EMP.DEPTNO = DEPT.DEPTNO AND DEPARTEMENT >= 20);
          SELECT ENAME, EMP.DEPTNO AS "DEPARTEMENT" FROM EMP JOIN DEPT ON (EMP.DEPTNO = DEPT.DEPTNO AND DEPARTEMENT >= 20)
                                                                                                                                                                                                *
          ERROR at line 1:
          ORA-00904: "DEPARTEMENT": invalid identifier


============================================================================================
Un alias défini dans la clause SELECT est inconnu de la clause WHERE.
============================================================================================
Cas avec un alias OK.
          SQL> SELECT EMPNO, ENAME AS "NOM" FROM EMP WHERE ENAME LIKE 'SM%';
          EMPNO           NOM

          ----------------------------------
          7369                SMITH

Que l'alias NOM soit réutilisé avec ou sans guillemets, il reste inconnu dans le WHERE.
          SQL> SELECT EMPNO, ENAME AS "NOM" FROM EMP WHERE NOM LIKE 'SM%';

          SELECT EMPNO, ENAME AS "NOM" FROM EMP WHERE NOM LIKE 'SM%'
                                                                                                    *
          ERROR at line 1:
          ORA-00904: "NOM": invalid identifier

          SQL> SELECT EMPNO, ENAME AS "NOM" FROM EMP WHERE "NOM" LIKE 'SM%';
          SELECT EMPNO, ENAME AS "NOM" FROM EMP WHERE "NOM" LIKE 'SM%'
                                                                                                    *
          ERROR at line 1:
          ORA-00904: "NOM": invalid identifier


============================================================================================
Un alias défini dans la clause SELECT est inconnu de la clause GROUP BY.
============================================================================================

Cas avec un alias OK.
          SQL> SELECT JOB AS "TRAVAIL", COUNT(*) FROM EMP GROUP BY JOB ORDER BY COUNT(*) DESC;
          TRAVAIL           COUNT(*)
          ----------------------------------------
          CLERK                     4
          SALESMAN              4
          MANAGER               3
          ANALYST                 2
          PRESIDENT             1

Exemple où l'alias TRAVAIL dans la clause SELECT est inconnu du GROUP BY.
          SQL> SELECT JOB AS "TRAVAIL", COUNT(*) FROM EMP GROUP BY TRAVAIL ORDER BY COUNT(*) DESC;
          SELECT JOB AS "TRAVAIL", COUNT(*) FROM EMP GROUP BY TRAVAIL ORDER BY COUNT(*) DESC
                                                                                                          *
          ERROR at line 1:
          ORA-00904: "TRAVAIL": invalid identifier


============================================================================================
Un alias défini dans la clause SELECT est inconnu de la clause HAVING
============================================================================================

Cas avec un alias OK.
          SQL> SELECT JOB, COUNT(*) AS NOMBRE FROM EMP GROUP BY JOB HAVING COUNT(*) > 2 ORDER BY COUNT(*) DESC;
          JOB           NOMBRE
          -------------------------------
          CLERK           4
          SALESMAN    4
          MANAGER     3

Exemple où l'alias NOMBRE dans un SELECT est inconnu du HAVING.
          SQL> SELECT JOB, COUNT(*) AS NOMBRE FROM EMP GROUP BY JOB HAVING NOMBRE > 2 ORDER BY COUNT(*) DESC;
          SELECT JOB, COUNT(*) AS NOMBRE FROM EMP GROUP BY JOB HAVING NOMBRE > 2 ORDER BY COUNT(*) DESC
                                                                                                                              *
          ERROR at line 1:
          ORA-00904: "NOMBRE": invalid identifier


============================================================================================
ET ENFIN, LE SEUL CAS AVEC UN ALIAS DEFINI DANS LE SELECT QUI EST CONNU DANS UNE AUTRE CLAUSE : LE ORDER BY!!!!!
============================================================================================
Exemple, l'alias NOMBRE défini dans la clause SELECT est utilisable dans le ORDER BY.
          SQL> SELECT JOB, COUNT(*) AS NOMBRE FROM EMP GROUP BY JOB ORDER BY NOMBRE DESC;

          JOB           NOMBRE
          -------------------------------
          CLERK           4
          SALESMAN    4
          MANAGER     3
          ANALYST       2
          PRESIDENT   1