John John - 6 months ago 21
SQL Question

How to alias column in SQL plus

When I write this query in sql plus show me error (ORA-00998: must name this expression with a column alias)

create or replace view vw_salary as select dname ,
(select count(*) from employee where dno=department.dnumber) from department;

Answer

Columns in a view need to have a name. So, you need as after the subquery:

create or replace view vw_salary as
    select dname,
           (select count(*) from employee e where e.dno = d.dnumber
           ) as NumEmployees
    from department d; 

I strongly encourage you to use table aliases and qualified column names (that is use the table alias). This is particularly important for correlated subqueries, where it is easy to make a mistake and that is hard to debug.

I also note that the view is called vw_salary, but there is no salary information.