solution for ORA-00933

Question

I'm tring to use join but facing this issue. I've pasted my query

select count(*) from table_a inner 
                join table_b on table_a.number = table_b.number 
                left outer join table_c on table_a.id = table_c.id 
                     and table_a.number = table_c.number 
          order by number;

pls let me know what is wrong in the query...

-Vinod

Answer 1

Are you executing this query as part of an INSERT or DELETE? If so, remove the ORDER BY. It's not needed anyway.

Error: ORA-00933: SQL command not properly ended

Cause: You tried to execute an SQL statement with an inappropriate clause.

Action: The options to resolve this Oracle error are:

You may have executed an INSERT statement with an ORDER BY Clause. To resolve this, remove the ORDER BY clause and re-execute the INSERT statement. For example, you tried to execute the following INSERT statement:

INSERT INTO supplier (supplier_id, supplier_name) VALUES (24553, 'IBM') ORDER BY supplier_id;

You can correct the INSERT statement by removing the ORDER BY clause as follows:

INSERT INTO supplier (supplier_id, supplier_name) VALUES (24553, 'IBM');

You may have tried to execute a DELETE statement with an ORDER BY Clause. To resolve this, remove the ORDER BY clause and re-execute the DELETE statement. For example, you tried to execute the following DELETE statement:

DELETE FROM supplier WHERE supplier_name = 'IBM' ORDER BY supplier_id;

You can correct the DELETE statement by removing the ORDER BY clause as follows:

DELETE FROM supplier WHERE supplier_name = 'IBM';

Answer 2

How did you execute this query?

In Oracle SQL, there's no such thing as a statement separator like ";". That one is only used in PL/SQL and some tools allow you to put more than one statement in a file/editor, when you separate them with ";". Only so that they can execute them separately.

Long story short: remove the ";" and try again. Oh and next time, tell us how you ran the query. We have to check our crystal balls to guess what your problem is.

Answer 3

When you transcribed your query to the anodyne test case you present here you inadvertently corrected it. Well, you introduced an ORA-00918 bug but once that is fixed the code runs fine...

SQL> create table table_a (col_1 number, id number)
  2  /

Table created.

SQL> create table table_b (col_1 number)
  2  /

Table created.

SQL> create table table_c (col_1 number, id number)
  2  /

Table created.

SQL>
SQL>
SQL> select count(*) from
  2  table_a inner join table_b on table_a.col_1 = table_b.col_1
  3  left outer join table_c on table_a.id = table_c.id
  4                         and table_a.col_1 = table_c.col_1
  5  order by col_1
  6  /
order by col_1
         *
ERROR at line 5:
ORA-00918: column ambiguously defined


SQL> select count(*) from
  2  table_a inner join table_b on table_a.col_1 = table_b.col_1
  3  left outer join table_c on table_a.id = table_c.id
  4                         and table_a.col_1 = table_c.col_1
  5  order by table_a.col_1
  6  /

  COUNT(*)
----------
         0

SQL>

Note: I have subsituted COL_1 for NUMBER as a column name. I don't think that's your problem, because using NUMBER unescaped in the query would hurl ORA-1747 not ORA-00933.

So, let's rule out the obvious: are you running on an ancient version of Oracle which doesn't support the ANSI join syntax, that is 8i or older?

Answer 4

You cannot order by a value which cannot be included in the result set. your result set aggregates multiple rows, each with its own value of NUMBER, into a single row. therefore the order by does not make logical sense. In this case your query only returns one row so ORDER BY is irrelevant.