Columns alias required while creating view - why?

Question

today, when i was creating view I've came across one of the silliest error in oracle. Getting to the point, I tried to execute this simple query:

CREATE VIEW dept_agg AS 
SELECT department_id, MIN(salary), MAX(salary)
FROM employees
GROUP BY department_id;

but i got error saying:

ORA-00998: must name this expression with a column alias 00998. 00000 - "must name this expression with a column alias"

Can anyone explain me why columns alias are so needed by Oracle in above query? Especially that alias aren't needed when I'm not creating view and just running the select statement?

Answer 1

You are creating a view and the view needs names for columns, just like a table does. This is usually done using aliases:

CREATE VIEW dept_agg AS    
    SELECT department_id, MIN(salary) as min_salary, MAX(salary) as max_salary
    FROM employees
    GROUP BY department_id;

You can also include the names as part of the view itself:

CREATE VIEW dept_agg(department_id, min_salary, max_salary) AS    
    SELECT department_id, MIN(salary), MAX(salary)
    FROM employees
    GROUP BY department_id;

Answer 2

You are probably curious, if you run this, it works

SELECT department_id, MIN(salary), MAX(salary)
FROM employees

But as part of the view, this is an issue. This is because your query window, whatever it is, simply displays what you entered for the field. This is like, dynamic name, not stored into DB. But to store the definition, oracle has the rule

Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#)

Hence you need to provide legal aliases for your dynamic columns. And how to do it is explained by @Gordon... in his answer