CODEWITHSUNDEEP
javamysqlsqlcommandsyntax-error
mzbhura
mzbhura

Reputation: 393

Why is sql command not properly ended being thrown?

I am running a query using SQL embedded in Java. I have three tables in my database, QUERY, G1, and G2. All of them have the same schema of (SETID, GENEID).

The query I am running is as follows:

SELECT q.SETID, COUNT(q.SETID), g1.SETID, COUNT(g1.SETID) 
FROM QUERY AS q 
INNER JOIN G1 AS g1 ON q.GENEID = g1.GENEID 
GROUP BY q.SETID, g1.SETID

Partial code for execution:

DriverManager.registerDriver(new oracle.jdbc.OracleDriver());
con = DriverManager.getConnection(url, user, password);
stmt = con.createStatement();
String sql = "SELECT q.SETID, COUNT(q.SETID) AS QSIZE, g1.SETID, COUNT(g1.SETID) AS GSIZE FROM QUERY AS q INNER JOIN G1 AS g1 ON q.GENEID = g1.GENEID GROUP BY q.SETID, g1.SETID";
rs = stmt.executeQuery(sql);
rsmd = rs.getMetaData();

When I run this, it throws the following error: ORA-00933: SQL command not properly ended. Could anyone please help me with this error?

Thanks!

Upvotes: 1

Views: 688

Answers (1)

Andrey Morozov
Andrey Morozov

Reputation: 7979

Try to:

  1. Escape table name QUERY - it looks like reserved word
  2. Add aliases to COUNT() columns - for example COUNT(q.SETID) as Q_CNT
  3. Add semi-colon at the end
  4. Remove table aliases like @harvey suggested
SELECT q.SETID, COUNT(q.SETID) as Q_CNT, g1.SETID, COUNT(g1.SETID) as G1_CNT
FROM `QUERY` AS q 
INNER JOIN G1 AS g1 ON q.GENEID = g1.GENEID 
GROUP BY q.SETID, g1.SETID;

SELECT QUERY.SETID, COUNT(QUERY.SETID) as Q_CNT, G1.SETID, COUNT(G1.SETID) as G1_CNT
FROM QUERY
INNER JOIN G1 ON QUERY.GENEID = G1.GENEID 
GROUP BY QUERY.SETID, G1.SETID;

Upvotes: 1

Related Questions