Oracle SQL: how to call created columns (alias) for pivot tables in a subquery

This is my first question in this community. It has helped me a lot before, so thank you all for being out there!

I have a problem with ORACLE PLSQL, I’m trying to create a pivot table that counts the number of people that are in a given salary range. I want cities as rows and salary_range as columns. My problem is when I select a column alias for the pivot table.

In table A I have the rows of all employees and their salaries, and in table B, I have their city. Both of them are linked by a key column named id_dpto. First, I join both tables selecting employee names, salaries, and cities. Second, I use CASE WHEN to create the range of salaries (less than 1000 and between 1000 and 2500 dollars) and give it the column alias SALARY_RANGE. Until here, everything is ok and the code runs perfectly.

My problem is on the third step. I use a subquery and PIVOT command to create the pivot to count by cities and salary_range, but when I use the select command in the alias it doesn’t work, my error message is "'F'.'SALARY_RANGE' INVALID IDENTIFYER". Can you help me what is the proper way to select a created column (salary_range) in a pivot table? I’ve tried both, with the F after the from and without it.

Initial data base

| Name | salary |  city  |
| ---- | ------ | ------ |
|john  | 999    | NY     | 
|adam  | 500    | NY     |
|linda | 1500   | NY     |
|Matt  | 2000   | London |
|Joel  | 1500   | London |

Desired result:

city	salary less than 1000	salary between 1000 and 2500
NY	2	1
London	0	2

My code:

SELECT F.SALARY_RANGE, F.CITY 
FROM (SELECT A.NAMES,
             A.SALARY,
             C.CITY,
             CASE
                   WHEN SALARY < 1000 THEN 'LESS THAN  1000'
                   WHEN SALARY < 2500 THEN 'BETWEEN 1000 AND 2500'
             END AS SALARY_RANGE FROM EMPLOYEES A 
       LEFT JOIN XXX B ON A.ID_DPTO = B.ID_DPTO) F
PIVOT
   (COUNT(SALARY_RANGE)
   FOR SALARY_RANGE IN ('LESS THAN 1000', 'BETWEEN 1000 AND 2500')
     )

Thanks for helping me!

Answer

I think you should use * and exclude SALARY and NAMES from subquery:

 SELECT *
  FROM (SELECT B.CITY,
           CASE
             WHEN SALARY < 1000 THEN
              'LESS THAN 1000'
             WHEN SALARY < 2500 THEN
              'BETWEEN 1000 AND 2500'
           END AS SALARY_RANGE
      FROM EMPLOYEES A
      LEFT JOIN XXX B
        ON A.ID_DPTO = B.ID_DPTO) F
     PIVOT(COUNT(SALARY_RANGE)
       FOR SALARY_RANGE IN('LESS THAN 1000', 'BETWEEN 1000 AND 2500'))

Advertisement

Answer