Error while executing query "Subquery is not a single column query; SQL statement:"

[Ramesh Elkaturi]

Hi All,

 

           I have tried to execute below query then getting error as "Subquery is not a single column query; SQL statement:". When i execute same query on DB2..it is working fine. I have googled the error and found that H2 and Derby databases does not support this feature. Can any one have the solution/work around please share with me.

Query:

SELECT * FROM REPORTINFO  WHERE (REPORTINFOID , VERSION ) IN (SELECT REPORTINFOID , MAX(VERSION ) AS VERSION  FROM REPORTINFO  WHERE DELETED =FALSE GROUP BY REPORTINFOID )

Error Details:

Subquery is not a single column query; SQL statement:
SELECT * FROM REPORTINFO  WHERE (REPORTINFOID , VERSION ) IN (SELECT REPORTINFOID , MAX(VERSION ) AS VERSION  FROM REPORTINFO  WHERE DELETED =FALSE GROUP BY REPORTINFOID ) [90052-176] 90052/90052 (Help)
org.h2.jdbc.JdbcSQLException: Subquery is not a single column query; SQL statement:
SELECT * FROM REPORTINFO  WHERE (REPORTINFOID , VERSION ) IN (SELECT REPORTINFOID , MAX(VERSION ) AS VERSION  FROM REPORTINFO  WHERE DELETED =FALSE GROUP BY REPORTINFOID ) [90052-176] 
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:344) 
    at org.h2.message.DbException.get(DbException.java:178) 
    at org.h2.message.DbException.get(DbException.java:154) 
    at org.h2.message.DbException.get(DbException.java:143) 
    at org.h2.expression.ConditionInSelect.optimize(ConditionInSelect.java:117) 
    at org.h2.command.dml.Select.prepare(Select.java:834) 
    at org.h2.command.Parser.prepareCommand(Parser.java:248) 
    at org.h2.engine.Session.prepareLocal(Session.java:442) 
    at org.h2.engine.Session.prepareCommand(Session.java:384) 
    at org.h2.jdbc.JdbcConnection.prepareCommand(JdbcConnection.java:1188) 
    at org.h2.jdbc.JdbcStatement.executeInternal(JdbcStatement.java:172) 
    at org.h2.jdbc.JdbcStatement.execute(JdbcStatement.java:160) 
    at org.h2.server.web.WebApp.getResult(WebApp.java:1391) 
    at org.h2.server.web.WebApp.query(WebApp.java:1064) 
    at org.h2.server.web.WebApp$1.next(WebApp.java:1026) 
    at org.h2.server.web.WebApp$1.next(WebApp.java:1013) 
    at org.h2.server.web.WebThread.process(WebThread.java:168) 
    at org.h2.server.web.WebThread.run(WebThread.java:94) 
    at java.lang.Thread.run(Thread.java:745) 

[Ramesh Elkaturi]

Hi all,

        I tried to execute below query then it is returning error saying that "Subquery is not a single column query; SQL statement:". but it is working on other databases like DB2, Datacom etc...I have googled the issue some of the folks are saying this feature not supported by H2 and Derby. Can any body suggest me the solution or work around for this.

       My Query: 

[Steve McLeod]

Have you tried concatenating the two columns into one column?

I don't know if that would work, just an idea.

PS: I think you'd have more success finding a solution if you post this on stackoverflow.com

[Noel Grandin]

we don't support multiple-column-IN queries, you'll need to rewrite it as a JOIN​