I noticed this in Oracle 11. However, I do not think it is a bug as the same thing happens in Oracle 9. I joined two SELECT * statements with a UNION and added an ORDER BY at the end. This gave me an ORA-00904. It seemed to me that the ORDER BY did not know the names of the columns in the output from the SELECT *:
SQL> create table andrew (col1 number)
2 /
Table created.
SQL> create table john (col1 number)
2 /
Table created.
SQL> select *
2 from user_tables
3 where table_name = 'JOHN'
4 union
5 select *
6 from user_tables
7 where table_name = 'ANDREW'
8 order by table_name
9 /
order by table_name
*
ERROR at line 8:
ORA-00904: "TABLE_NAME": invalid identifier
SQL>
I found the following workarounds. One of them may be suitable if you hit this problem yourself. I used SELECT TABLE_NAME instead of SELECT *:
SQL> select table_name
2 from user_tables
3 where table_name = 'JOHN'
4 union
5 select table_name
6 from user_tables
7 where table_name = 'ANDREW'
8 order by table_name
9 /
TABLE_NAME
------------------------------
ANDREW
JOHN
SQL>
I removed the ORDER BY. For the purposes of this example, I also added SET HEAD OFF and removed some blank lines from the output:
SQL> set head off
SQL> select *
2 from user_tables
3 where table_name = 'JOHN'
4 union
5 select *
6 from user_tables
7 where table_name = 'ANDREW'
8 /
ANDREW
USERS
VALID 10
1 255 65536
1 2147483645
YES N
1 1 N ENABLED
NO N N NO DEFAULT DISABLED NO
NO DISABLED YES
DISABLED DISABLED
NO NO
JOHN
USERS
VALID 10
1 255 65536
1 2147483645
YES N
1 1 N ENABLED
NO N N NO DEFAULT DISABLED NO
NO DISABLED YES
DISABLED DISABLED
NO NO
SQL>
I looked at the USER_TABLES view, saw that TABLE_NAME was the first column and used ORDER BY 1 instead:
SQL> desc user_tables
Name Null? Type
-------------------------- -------- ------------------
TABLE_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(30)
IOT_NAME VARCHAR2(30)
STATUS VARCHAR2(8)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(3)
BACKED_UP VARCHAR2(1)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
AVG_SPACE_FREELIST_BLOCKS NUMBER
NUM_FREELIST_BLOCKS NUMBER
DEGREE VARCHAR2(10)
INSTANCES VARCHAR2(10)
CACHE VARCHAR2(5)
TABLE_LOCK VARCHAR2(8)
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
PARTITIONED VARCHAR2(3)
IOT_TYPE VARCHAR2(12)
TEMPORARY VARCHAR2(1)
SECONDARY VARCHAR2(1)
NESTED VARCHAR2(3)
BUFFER_POOL VARCHAR2(7)
ROW_MOVEMENT VARCHAR2(8)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
SKIP_CORRUPT VARCHAR2(8)
MONITORING VARCHAR2(3)
CLUSTER_OWNER VARCHAR2(30)
DEPENDENCIES VARCHAR2(8)
COMPRESSION VARCHAR2(8)
COMPRESS_FOR VARCHAR2(18)
DROPPED VARCHAR2(3)
READ_ONLY VARCHAR2(3)
SQL> select *
2 from user_tables
3 where table_name = 'JOHN'
4 union
5 select *
6 from user_tables
7 where table_name = 'ANDREW'
8 order by 1
9 /
ANDREW
USERS
VALID 10
1 255 65536
1 2147483645
YES N
1 1 N ENABLED
NO N N NO DEFAULT DISABLED NO
NO DISABLED YES
DISABLED DISABLED
NO NO
JOHN
USERS
VALID 10
1 255 65536
1 2147483645
YES N
1 1 N ENABLED
NO N N NO DEFAULT DISABLED NO
NO DISABLED YES
DISABLED DISABLED
NO NO
SQL>
I put the UNION inside brackets and put the ORDER BY outside the brackets:
SQL> select * from
2 (select *
3 from user_tables
4 where table_name = 'JOHN'
5 union
6 select *
7 from user_tables
8 where table_name = 'ANDREW')
9 order by table_name
10 /
ANDREW
USERS
VALID 10
1 255 65536
1 2147483645
YES N
1 1 N ENABLED
NO N N NO DEFAULT DISABLED NO
NO DISABLED YES
DISABLED DISABLED
NO NO
JOHN
USERS
VALID 10
1 255 65536
1 2147483645
YES N
1 1 N ENABLED
NO N N NO DEFAULT DISABLED NO
NO DISABLED YES
DISABLED DISABLED
NO NO
SQL>
The doc specifies you must use aliases or position.
ReplyDeletePosition is safer, but alias is also acceptable if you have the in all subqueries.
What does not work
select 2 x, 3, 1 from dual
union
select 1, 2, 3 from dual
union
select 3, 1, 2 from dual
order by x;
Gives ora-904 because x is not present in the line before last
The following works but is a distraction
select 2 x, 3, 1 from dual
union
select 1, 2, 3 x from dual
union
select 3, 1, 2 from dual
order by x;
The best is to user "ORDER BY 1"
Check For compound queries containing set operators UNION, INTERSECT, MINUS, or UNION ALL, the ORDER BY clause must specify positions or aliases rather than explicit expressions
Dear Laurent,
ReplyDeleteThank you for your comment and, although it's a bit late now, I wish you a happy new year.
Kind regards,
Andrew