How to use ALIASES in Oracle correctly?

Asked

Viewed 5,974 times

5

I’m trying to select several columns from a single table, and to facilitate the process of viewing the data and making it easier to separate the columns, I decided to use aliases.

But the problem is that when running the query, it returns me the following error :

ORA-00923: FROM keyword not located where expected

My code is like this :

SELECT MAX(salary) as 'Maximo Salário' , 
MIN(salary) as 'Minimo Salário', 
SUM(salary) as 'Soma de Todos Salários', 
AVG(salary) as 'Média Salarial' FROM employees

According to the Oracle documentation itself, a column of a table with aliases is selected as follows : 

column_name AS alias_name

So what am I doing wrong here ? There should be some WHERE in this SELECT ? But it is not mandatory to have it, only in cases where it is necessary to use it in fact.

How can I use aliases correctly in this SELECT ?

oracle alias

  • Has the answer solved your problem? If not, is there anything I can do to improve it?

  • Solved yes, I just forgot to put as a right answer. Anyway, I put it now and again thank you for the help :)

2 answers

6


Names need to be in double quotes, not simple.

SELECT MAX(salary) as "Maximo Salário" , 
MIN(salary) as "Minimo Salário", 
SUM(salary) as "Soma de Todos Salários", 
AVG(salary) as "Média Salarial" FROM employees
  • 2

    My mistake was somewhat crude, but still, thank you very much for responding so quickly, it worked perfectly. + 1 and right answer (you have to wait a while to put yourself as the right answer).

  • @Monteiro These things happen. Nice to be able to help.

1

Complemented by a few points:

The use of AS when used to name columns is optional, example:

SELECT 
      MAX(salary) "Maximo Salário" , 
      MIN(salary) "Minimo Salário", 
      SUM(salary) "Soma de Todos Salários", 
      AVG(salary) "Média Salarial" 
FROM employees

If the alias to be chosen has no more than one word, you can refrain from using double quotes, example:

SELECT 
  MAX(salary) MaximoSalario , 
  MIN(salary) MinimoSalario, 
  SUM(salary) SomaDeTodosSalarios, 
  AVG(salary) MediaSalarial 
FROM employees

There is the possibility of referencing an alias in ORDER BY, example:

SELECT 
  MAX(salary) "Maximo Salario" , 
  MIN(salary) MinimoSalario, 
  SUM(salary) SomaDeTodosSalarios, 
  AVG(salary) MediaSalarial,
  dep
FROM employees
group by dep
order by "Maximo Salario"

Another useful use of AS and the construction of a QUERY using SUB QUERY, example:

WITH 
   avg_sal AS (
      SELECT AVG(salary) avg FROM employees),
   emp_dep AS (
      SELECT name, salary FROM employees where dep = 10)


SELECT * FROM emp_dep where salary > (select avg from avg_sal)


Remarks:

  • Using double quotes, Oracle is case-sensitive.
  • If you do not use double quotes, Oracle will interpret everything as uppercase.
  • Avoid using the same identifier for two columns.
  • Each version of Oracle has a specific limit for an identifier.

In this example, internally Oracle will define the identified MAXIMO for both columns, if you try to use the identified one, you will receive an error: ORA-00960:

SELECT 
  MAX(salary) "MAXIMO" , 
  MIN(salary) maximo
FROM employees
order by maximo

Browser other questions tagged oracle alias

You are not signed in. Login or sign up in order to post.