Oracle11g - SELECT command problems

1

I have the following error:

  

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

This happens after I run the following line of 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 MEETING table has the following attributes:

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

The MEETING_LIST table has the following attributes:

  • ID, ME_ID, ID_UTILIZER, INVITE, STATUS

The UTILIZADOR table has the following attributes:

  • ID, NAME, WORDER

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

    
asked by anonymous 23.10.2014 / 12:03

4 answers

4

The error message itself is telling you 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 reached the end of the command before actually being at the end of the command.

I think your problem is the aliases you put in. Actually you should not put " MEETING AS " and only " MEETING M " should be used only for " AS " define the 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));

Try to always have some IDE for database management, such as the PL / SQL Developer and whenever these errors occur execute your SQL commands in the IDE, it helps a lot in the agility in finding the errors.

    
23.10.2014 / 14:04
2

Solution is to remove 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));
    
23.10.2014 / 14:51
0

There are two more parentheses in the WHERE clause causing the 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
    
23.10.2014 / 14:25
-1

When you run sql on an oracle client, right or wrong? if you play sql in squirrel or other error sql client?

  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_UTILIZER = (SELECT ID FROM USER WHERE USERNAME = ' user_id ') AND ML.STATUS = 1 AND ML.INVITE = 1)

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

Try to see if this query here solves:

  

SELECT      M.ID,      M.TITLE,      DAY M,      M.HORA   FROM      MEETING AS M,      MEETING_LIST AS ML   ONDE      M.ID = ML.ID_MEETING      AND ML.ID_UTILIZER = (         SELECT            ID         FROM            USER         ONDE            NAME OF APPLICANT = '1'      )      AND ML.STATUS = 1      AND ML.INVITE = 1

    
23.10.2014 / 13:42