Search code examples
javasqlderby

SQL syntax error on WHERE clause using apache Derby

For the following SQL select statement ; - 

resultSet = statement.executeQuery("SELECT SS100.ORDERS.ORDER_ID, SS100.ORDERS.ORDER_NUMBER," +" SS100.ORDERS.PERSON_ID, SS100.PERSON.FIRST_NAME"+
"FROM  PERSON, ORDERS " +
"WHERE SS100.PERSON.PERSON_ID = SS100.ORDERS.PERSON_ID " );

I'm getting a syntax error on the WHERE keyword - dump as follows :-

 java.sql.SQLSyntaxErrorException: Syntax error: Encountered "WHERE" at line 1, column 126.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.execute(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedStatement.executeQuery(Unknown Source)
    at Database.orderQuery(Database.java:146)
    at MainApp.main(MainApp.java:19)

Caused by: java.sql.SQLException: Syntax error: Encountered "WHERE" at line 1, column 126.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)... 10 more

I've tried playing around with the alias statement, but then get an error on the '.' dot operator. I'm obviously doing something fundamentally wrong but I can't see it at the moment, can anyone help?

Solution

  • Look here:

    resultSet = statement.executeQuery("SELECT ..." +... + " SS100.ORDERS.PERSON_ID, SS100.PERSON.FIRST_NAME"+
    "FROM  PERSON, ORDERS " +

    There's no space between "SS100.PERSON.FIRST_NAME" and "FROM".

    This can be easily solved by always starting your parts with an space:

    resultSet = statement.executeQuery("SELECT ..." +... + " SS100.ORDERS.PERSON_ID, SS100.PERSON.FIRST_NAME"+
    " FROM  PERSON, ORDERS " +
    " WHERE ..."