1

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!

azdiiv
  • 383
  • 5
  • 17
  • Just a guess: Is it because of the missing semi-colon? Probably not, but I just want to be sure. – AdamMc331 Nov 02 '14 at 21:46
  • 1
    Post the actual code executing the statement. – user432 Nov 02 '14 at 21:47
  • 1
    Ubitsoft and piliapp both say it's fine, is there more code wrapping it that we could look at? – BIU Nov 02 '14 at 21:48
  • There is a known issue with the oracle driver and using ' AS ' for table aliasing. Try the following suggestion; http://stackoverflow.com/questions/21145028/how-to-use-the-as-keyword-to-alias-a-table-in-oracle – harvey Nov 02 '14 at 22:06
  • Just added in the code used during execution. Also I tried to remove 'AS', but then it started to throw an invalid character error. – azdiiv Nov 02 '14 at 22:24
  • 1
    You should remove the `AS` only from the FROM and INNER JOIN. – RealSkeptic Nov 02 '14 at 22:43

1 Answers1

1

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;
Andrey Morozov
  • 7,839
  • 5
  • 53
  • 75
  • I can't escape the table name as it throws an error saying it is an invalid character. The semi-colon and aliases also didn't seem to fix the issue. – azdiiv Nov 02 '14 at 22:36