ORA-00904: ORDER BY with UNION ALL

oracle-sqldeveloper, sql

Solution

In SQL, you cannot name the columns in ORDER BY with a composite statement, you have to use column position.

Or,

Project the columns explicitly.

In your case, since you have concatenated the columns, you cannot project them explicitly, also, column position would not be of any sense. You could therefore play a small trick. Add a pseudo column, with required values to the rows you want to be sorted first, and then use NULL value in the pseudo column for which you want the sorting after the first column. So that, the NULLs are always placed in the end of the sort.

For example,

SQL> SELECT filerec FROM (
  2      SELECT 'FILENAME' AS filerec, 1 col FROM dual
  3      UNION ALL
  4      SELECT 'FILEDATE: ' || to_char(SYSDATE,'mm/dd/yyyy') as filerec, 2 col FROM dual
  5      UNION ALL
  6      SELECT empno || ename AS filerec, NULL col FROM emp
  7      ORDER BY 2,1
  8  );

FILEREC
--------------------------------------------------
FILENAME
FILEDATE: 02/27/2015
7369SMITH
7499ALLEN
7521WARD
7566JONES
7654MARTIN
7698BLAKE
7782CLARK
7788SCOTT
7839KING
7844TURNER
7876ADAMS
7900JAMES
7902FORD
7934MILLER

16 rows selected.

SQL>

Problem

I'm creating a flat file with header. I'm getting an ORA-00904 error, which I think is because of the headers I created that does not have the field 'employee_name' (am I correct with that assumption?). If yes, how can I can I sort my query without the headers? The error I get: ``` ORA-00904: "employee_id":invalid identifier ``` My code: ``` select 'FILENAME' as filerec from dual UNION ALL select 'FILEDATE: ' || to_char(sysdate,'mm/dd/yyyy) as filerec from dual UNION ALL select employee_id || emloyee_name from employee_database as filerec order by employee_id; ``` This is the output I want produce: ``` FILENAME FILEDATE: 02/27/2015 200125Ruth Chan 200126Dan Gonzales 200135Lisa Mayoral ```

Original source