Sunday, January 13, 2013

ORA-00904

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>

2 comments:

Laurent Schneider said...

The doc specifies you must use aliases or position.

Position 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

Andrew Reid said...

Dear Laurent,

Thank you for your comment and, although it's a bit late now, I wish you a happy new year.

Kind regards,

Andrew