1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Using Column Alias in ORDER BY/GROUP BY/WHERE/HAVING

Discussion in 'SQL PL/SQL' started by Bhavna, Oct 4, 2012.

  1. Bhavna

    Bhavna Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    I know that a column alias cannot be used in a GROUP BY clause

    Can it be used in ORDER BY, WHERE or HAVING clauses?
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    No:

    Code (SQL):
    SQL> SELECT empno enbr, ename enm, sal salary, hiredate hdt
      2  FROM emp
      3  WHERE enbr < 7400;
    WHERE enbr < 7400
          *
    ERROR at line 3:
    ORA-00904: "ENBR": invalid identifier

    SQL>
    SQL> SELECT deptno dep_nbr, avg(sal) avg_salary
      2  FROM emp
      3  GROUP BY dep_nbr;
    GROUP BY dep_nbr
             *
    ERROR at line 3:
    ORA-00904: "DEP_NBR": invalid identifier

    SQL>
    SQL> SELECT deptno dep_nbr, avg(sal) avg_salary
      2  FROM emp
      3  GROUP BY deptno
      4  HAVING avg_salary > 800;
    HAVING avg_salary > 800
           *
    ERROR at line 4:
    ORA-00904: "AVG_SALARY": invalid identifier

    SQL>
    SQL> SELECT deptno dep_nbr, avg(sal) avg_salary
      2  FROM emp
      3  GROUP BY dep_nbr
      4  HAVING avg_salary > 800;
    HAVING avg_salary > 800
           *
    ERROR at line 4:
    ORA-00904: "AVG_SALARY": invalid identifier

    SQL>
    Of course you can 'nest' queries in some cases to allow you to use the alias in such constructs:

    Code (SQL):
    SQL> SELECT * FROM
      2  (SELECT empno enbr, ename enm, sal salary, hiredate hdt
      3  FROM emp)
      4  WHERE enbr < 7400;
     
          ENBR ENM            SALARY HDT
    ---------- ---------- ---------- ---------
          7369 SMITH             800 17-DEC-80
     
    SQL>
    SQL> SELECT dep_nbr, avg(sal) avg_salary FROM
      2  (SELECT deptno dep_nbr, sal
      3  FROM emp)
      4  GROUP BY dep_nbr;
     
       DEP_NBR AVG_SALARY
    ---------- ----------
            30 1566.66667
            20       2175
            10 2916.66667
     
    SQL>
     
  3. Bhavna

    Bhavna Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Thanks!

    But they can be used in "ORDER BY" clause right?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You really need to install the Oracle database software, create a database and test these things yourself. Having people simply give you answers really doesn't help you.

    Provide the work that you've done to answer such questions should you have problems and we will assist you.
     
  5. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please test this yourself -- you need to understand when WHERE, GROUP BY, HAVING and ORDER BY are evaluated in the parsing process. Knowing that information will answer your questions.
     
  6. dariyoosh

    dariyoosh Forum Advisor

    Messages:
    118
    Likes Received:
    19
    Trophy Points:
    260
    @ Bhavna
    Seems that you're using some braindumps to prepare your exam :)
    There is no future that way, believe me !
     
  7. Bhavna

    Bhavna Active Member

    Messages:
    5
    Likes Received:
    0
    Trophy Points:
    55
    Yes I am using them. I thought they would give some good practice.

    I have finished reading books for the exam.