When I run the following query using Oracle's sample HR schema, I get an "invalid identifer" error:
SELECT 'countries' AS table_name, COUNT(*) AS num_records
FROM countries
UNION
SELECT 'departments', COUNT(*)
FROM departments
UNION
SELECT 'employees', COUNT(*)
FROM employees
UNION
SELECT 'job_history', COUNT(*)
FROM job_history
UNION
SELECT 'jobs', COUNT(*)
FROM jobs
UNION
SELECT 'locations', COUNT(*)
FROM locations
UNION
SELECT 'regions', COUNT(*)
FROM regions
ORDER BY num_records;
The specific error is:
ORA-00904: "NUM_RECORDS": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 21 Column: 10
The error goes away if I re-add the alias to one of the other query parts, but only one:
SELECT 'countries' AS table_name, COUNT(*) AS num_records
FROM countries
UNION
SELECT 'departments', COUNT(*)
FROM departments
UNION
SELECT 'employees', COUNT(*)
FROM employees
UNION
SELECT 'job_history', COUNT(*)
FROM job_history
UNION
SELECT 'jobs', COUNT(*)
FROM jobs
UNION
SELECT 'locations', COUNT(*) AS num_records
FROM locations
UNION
SELECT 'regions', COUNT(*)
FROM regions
ORDER BY num_records;
Notice that I have added AS num_records after COUNT(*) on the SELECT from locations. When I do this, the query runs as expected and I get results like:
TABLE_NAME NUM_RECORDS
----------- -----------
regions 4
job_history 11
jobs 19
locations 23
countries 25
departments 27
employees 108
7 rows selected.
Adding the alias to any other one of the query parts does not make the error go away. This feels very strange to me. Anyone know what's going on?
For what it's worth, it's not just with this schema. I found the same issue with another schema as well.
