Oracle11g - Problems SELECT command

Question

I’m making the following mistake:

java.sql.Sqlsyntaxerrorexception: ORA-00933: SQL command not properly ended

This happens after I run the following line from SQL:

/* Listar todas as reuniões do utilizador onde esteja convidado*/
String sql = "SELECT M.ID, M.TITLE, M.DIA, M.HORA FROM MEETING AS M, MEETING_LIST AS ML WHERE (M.ID=ML.ID_MEETING AND ML.ID_UTILIZADOR=( SELECT ID FROM UTILIZADOR WHERE NOMEUTILIZADOR='"+ parcel.getUsername()+"' ) AND ML.STATUS=1 AND ML.INVITE=1)";
toClient = new AbstractMessage(database.submitQuery(sql));

The table MEETING has the following attributes:

• ID, TITLE, OUTCOME, DAY, TIME, LOCATION

The table MEETING_LIST has the following attributes:

• ID, ID_MEETING, ID_UTILIZADOR, INVITE, STATUS

The table UTILIZADOR has the following attributes:

• ID, username, PALAVRACHAVE

The table MEETING_LIST is a table that crosses users and meetings (Many to Many).

Answer 1

The error message itself is telling what is happening.

ORA-00933: SQL command not properly ended

It means that something in the construction of your SQL made ORACLE understand that it came to the end of the command before actually being at the end of the command.

I believe your problem is the aliases you have put in. In fact you should not put in "MEETING AS M" and yes only "MEETING M" the reserved word "AS" should only be used to define column aliases and not reset the table name to be used in the SQL command.

Try the following.

String sql = "SELECT M.ID, M.TITLE, M.DIA, M.HORA FROM MEETING M, MEETING_LIST ML WHERE (M.ID=ML.ID_MEETING AND ML.ID_UTILIZADOR=( SELECT ID FROM UTILIZADOR WHERE NOMEUTILIZADOR='"+ parcel.getUsername()+"' ) AND ML.STATUS=1 AND ML.INVITE=1)";
toClient = new AbstractMessage(database.submitQuery(sql));

Always try to have in hand some IDE for database management such as PL/SQL Developer and whenever these errors occur run your SQL commands in the IDE, it greatly helps in finding errors quickly.

Answer 2

Solution is to remove the AS.

String sql = "SELECT M.ID, M.TITLE, M.DIA, M.HORA FROM MEETING M, MEETING_LIST ML WHERE (M.ID=ML.ID_MEETING AND ML.ID_UTILIZADOR=( SELECT ID FROM UTILIZADOR WHERE NOMEUTILIZADOR='"+ parcel.getUsername()+"' ) AND ML.STATUS=1 AND ML.INVITE=1)";
                toClient = new AbstractMessage(database.submitQuery(sql));

Answer 3

Has two extra parentheses in the WHERE clause causing error:

SELECT M.ID, M.TITLE, M.DIA, M.HORA
  FROM MEETING AS M, MEETING_LIST AS ML
WHERE (M.ID = ML.ID_MEETING AND
    ML.ID_UTILIZADOR =
       (SELECT ID<br>
           FROM UTILIZADOR<br>
          WHERE NOMEUTILIZADOR = '"+ parcel.getUsername()+"') AND
       ML.STATUS = 1 AND ML.INVITE = 1)

Try this:

SELECT M.ID, M.TITLE, M.DIA, M.HORA
  FROM MEETING AS M, MEETING_LIST AS ML
 WHERE M.ID = ML.ID_MEETING
   AND ML.ID_UTILIZADOR =
       (SELECT ID
          FROM UTILIZADOR
         WHERE NOMEUTILIZADOR = '"+ parcel.getUsername()+"')
   AND ML.STATUS = 1
   AND ML.INVITE = 1

Answer 4

When you run sql on a right or wrong oracle client? if vc play sql in Squirrel or other sql client error?

SELECT M.ID, M.TITLE, M.DIA, M.HORA FROM MEETING AS M, MEETING_LIST AS ML WHERE (M.ID=ML.ID_MEETING AND ML.ID_UTILIZADOR=( SELECT ID FROM USER WHERE USERNAME='id_do_user' ) AND ML.STATUS=1 AND ML.INVITE=1)

If sql is giving error in sql client then a debugging strategy would be you run the query in parts, this way you will know where is the point where the error occurs.

Try to see if this query here solves:

SELECT M.ID, M.TITLE, M.DIA, M.HORA FROM MEETING AS M, MEETING_LIST AS ML WHERE M.ID=ML.ID_MEETING AND ML.ID_UTILIZADOR=( SELECT ID FROM USER WHERE USERNAME='1 ' ) AND ML.STATUS=1 AND ML.INVITE=1