7 Replies Latest reply on Aug 29, 2011 7:33 PM by 883611

    Column alias error in a query of views

    883611
      Hi
      I was trying to workout this query

      create view dept_sal as
      select d.department_name,sum(e.salary)
      from departments d left outer join employees e
      on d.department_id= e.department_id
      group by d.department_name;

      and i recieved this error message .............


      Error at Command Line:2 Column:25
      Error report:
      SQL Error: ORA-00998: must name this expression with a column alias
      00998. 00000 - "must name this expression with a column alias"
      *Cause:   
      *Action:


      I tried to put an alias for sum(e.salary) function and it worked but i dont understand why it is required or is the problem somewhere else and why an alias is needed here ???

      Actually the book hasnt specfied the need for an alias at this place ...so i wanted to know why am getting this error ..

      create or replace view dept_sal as
      select d.department_name,sum(e.salary) as d_sal
      from departments d left outer join employees e
      on d.department_id= e.department_id
      group by d.department_name;


      view DEPT_SAL created.



      Also i wanted to know if i can start a thread each time i want have a problem or can i add my questions to somebody
      else's thread ..

      Thanks
      Jayshree
        • 1. Re: Column alias error in a query of views
          Bawer
          *** wrong ***

          Edited by: Bawer on 29.08.2011 15:50
          • 2. Re: Column alias error in a query of views
            Frank Kulash
            Hi, Jayshree,
            to_learn wrote:
            Hi
            I was trying to workout this query

            create view dept_sal as
            select d.department_name,sum(e.salary)
            from departments d left outer join employees e
            on d.department_id= e.department_id
            group by d.department_name;

            and i recieved this error message .............


            Error at Command Line:2 Column:25
            Error report:
            SQL Error: ORA-00998: must name this expression with a column alias
            00998. 00000 - "must name this expression with a column alias"
            *Cause:   
            *Action:


            I tried to put an alias for sum(e.salary) function and it worked but i dont understand why it is required or is the problem somewhere else and why an alias is needed here ???
            Every column in a table or view must have a unique name. If you don't explicitly specify a name for the output column, Oracle will try to generate one based on the input expression. If the name does not follow the normal rules for identifiers (one of which is that the name can't contain special symbols, like parentheses) then the name must be enclosed in double-quotes.
            Notice that these rules are a little different from the rules about result sets. In the result set of a main query, for example, column names do not have to be unique, and a system-generated name like 'SUM(E.SALARY)" is acceptable.
            Actually the book hasnt specfied the need for an alias at this place ...so i wanted to know why am getting this error ..
            I can't see which book you're reading. Post a quote. If the book is available on line, post a link, too.
            create or replace view dept_sal as
            select d.department_name,sum(e.salary) as d_sal
            from departments d left outer join employees e
            on d.department_id= e.department_id
            group by d.department_name;


            view DEPT_SAL created.
            That's the right way to do it.
            Also i wanted to know if i can start a thread each time i want have a problem or can i add my questions to somebody
            else's thread ..
            It's better to start your own thread.
            1 person found this helpful
            • 3. Re: Column alias error in a query of views
              Hoek
              Also i wanted to know if i can start a thread each time i want have a problem or can i add my questions to somebody else's thread ..
              Best thing you can do is start a new thread.
              That is after you've searched :
              - the Documentation
              - the forums
              and found nothing.
              Don't start new threads that are FAQ's....

              Adding questions to existing threads is called 'hi-jacking' a thread and is not appreciated. Especially when you're digging up old threads.
              • 4. Re: Column alias error in a query of views
                883611
                Thanks Frank for explaining the details .

                I too tried executing the query without the views & it worked well without the aliases .

                I am studying the OCA Oracle Database 11g SQL Fundamentals I Exam guide for IZ0-051 Author :John Watson & Roopesh Ramklass .

                i saw that all the other examples were using the aliases ..The one i quoted was the first ,and i posted the query before trying the others ..

                so what i get is from your reply is that we have to explicitly give a name to the column in  case of a 'view' and only in case of the aggregate  functions ?.

                Thanks Again
                Jayshree

                Edited by: to_learn on Aug 29, 2011 12:20 PM
                • 5. Re: Column alias error in a query of views
                  883611
                  Thanks Hoek
                  • 6. Re: Column alias error in a query of views
                    Frank Kulash
                    Hi,
                    to_learn wrote:
                    ... so what i get is from your reply is that we have to explicitly give a name to the column in  case of a 'view'?.
                    Close.
                    If the expression that defines an output column is just an input column (either alone, qualified by a table name or alias, as below, or qualified by schema_name.table_name), and that name is unique, then you don't need an alias:
                    create view dept_sal as 
                    select  d.department_name     -- no alias needed: this column will be called department_name by default
                    ,     sum (e.salary)           --    alias needed
                    from      departments d ...
                    There are some other cases where an alias isn't required, too, but they're not worth mentioning. When in doubt, give an alias.

                    It doesn't hurt to give an explicit alias when one isn't strictly required. For example:
                    create view dept_sal as 
                    select  d.department_name     AS department_name
                    ,     sum (e.salary)           AS total_salary
                    from      departments d ...
                    and only in case of the aggregate functions
                    No; there's nothing special about aggregate fucntions. Single-row fucntions, analytic fucntions and compound expressions have the same problem:
                    CREATE OR REPLACE VIEW     my_emp_view
                    AS
                    SELECT     empno
                    ,     ename || ': ' || job     AS ename_job
                    ,     ROUND (sal, -2)          AS sal_100
                    ,     ROW_NUMBER () OVER (ORDER BY hiredate, ename)
                                                  AS seniority_num
                    FROM    scott.emp
                    ;
                    The view above doesn't use any aggregate functions, but aliases are needed for all columns except empno.
                    1 person found this helpful
                    • 7. Re: Column alias error in a query of views
                      883611
                      Thanks Frank ..That solves my problem


                      Jayshree