为什么sql命令没有正确结束被抛出?
问题描述:
我正在使用嵌入在Java中的SQL运行查询。我的数据库中有三个表格,QUERY,G1和G2。它们都具有相同的(SETID,GENEID)模式。为什么sql命令没有正确结束被抛出?
我运行的查询如下:
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
执行的部分代码:
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();
当我运行它,它会引发以下错误: ORA-00933:SQL命令没有妥善结束。任何人都可以请帮我解决这个错误?
谢谢!
答
尝试:
- 逃生表名
QUERY
- 它看起来像保留字 - 添加别名COUNT()列 - 例如
COUNT(q.SETID) as Q_CNT
- 末
- 添加分号删除表格别名,如@harvey建议
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;
+0
我不能逃避表名,因为它会抛出错误,说它是无效字符。分号和别名似乎也没有解决问题。 – Asdeev 2014-11-02 22:36:19
+1
好的...查看帖子中的更新 – 2014-11-03 05:53:57
只是一个猜测:是不是因为缺少分号的?可能不会,但我只想确定。 – AdamMc331 2014-11-02 21:46:08
发布执行语句的实际代码。 – user432 2014-11-02 21:47:54
Ubitsoft和piliapp都说这很好,是否有更多的代码包装它,我们可以看看? – BIU 2014-11-02 21:48:21