Is there any way to use sub-queries in CASE statement ?
kunal mahale
Hi Julian,
I am trying to put a sub-query in CASE statement, not so sure if optiq support such CASES. Is there any way to do this ?
The queries are attached herewith :
Query 1:
select e.NAME,
(CASE e.dept_ID WHEN (Select d.id from PV_ADMIN.dept d where d.id = e.dept_id)
THEN (Select d.name from PV_ADMIN.dept d where d.id = e.dept_id)
ELSE 'DepartmentNotFound' END ) AS DEPTNAME
from PV_ADMIN.EMP e;
ERROR IN OPTIQ 1:
java.sql.SQLException: exception while executing query
at net.hydromatic.avatica.Helper.createException(Helper.java:40)
at net.hydromatic.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:406)
at net.hydromatic.avatica.AvaticaStatement.executeQueryInternal(AvaticaStatement.java:350)
at net.hydromatic.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:78)
at net.hydromatic.optiq.impl.mongodb.TestOptiq.main(TestOptiq.java:592)
Caused by: java.lang.RuntimeException: while executing SQL [SELECT "$f0",("NAME") "$f1"
FROM (SELECT "t4"."$f0", "DEPT0"."NAME"
FROM (SELECT "$f0"
FROM (SELECT "EMP"."DEPT_ID" "$f0"
FROM "PV_ADMIN"."EMP"
LEFT JOIN (SELECT "$f0",("ID") "$f1"
FROM (SELECT "t0"."$f0", "DEPT"."ID"
FROM (SELECT "$f0"
FROM (SELECT "DEPT_ID" "$f0"
FROM "PV_ADMIN"."EMP") "t"
GROUP BY "$f0") "t0"
INNER JOIN "PV_ADMIN"."DEPT" ON "t0"."$f0" = "DEPT"."ID") "t1"
GROUP BY "$f0") "t2" ON "EMP"."DEPT_ID" = "t2"."$f0") "t3"
GROUP BY "$f0") "t4"
INNER JOIN "PV_ADMIN"."DEPT" "DEPT0" ON "t4"."$f0" = "DEPT0"."ID") "t5"
GROUP BY "$f0"]
at net.hydromatic.optiq.runtime.ResultSetEnumerable.enumerator(ResultSetEnumerable.java:144)
at net.hydromatic.linq4j.EnumerableDefaults.toLookup_(EnumerableDefaults.java:1898)
at net.hydromatic.linq4j.EnumerableDefaults.toLookup(EnumerableDefaults.java:1891)
at net.hydromatic.linq4j.EnumerableDefaults.toLookup(EnumerableDefaults.java:1867)
at net.hydromatic.linq4j.DefaultEnumerable.toLookup(DefaultEnumerable.java:663)
at net.hydromatic.linq4j.EnumerableDefaults$5.enumerator(EnumerableDefaults.java:886)
at Baz$11$1.<init>(Unknown Source)
at Baz$11.enumerator(Unknown Source)
at net.hydromatic.optiq.jdbc.OptiqPrepare$PrepareResult.enumerator(OptiqPrepare.java:262)
at net.hydromatic.optiq.jdbc.OptiqPrepare$PrepareResult.createCursor(OptiqPrepare.java:230)
at net.hydromatic.optiq.jdbc.MetaImpl.createCursor(MetaImpl.java:597)
at net.hydromatic.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:162)
at net.hydromatic.optiq.jdbc.OptiqResultSet.execute(OptiqResultSet.java:52)
at net.hydromatic.optiq.jdbc.OptiqResultSet.execute(OptiqResultSet.java:29)
at net.hydromatic.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:404)
... 3 more
Caused by: java.sql.SQLSyntaxErrorException: ORA-00979: not a GROUP BY expression
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:440)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396)
at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:837)
at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:445)
at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:191)
at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:523)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193)
at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:852)
at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1153)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1267)
at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1477)
at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:392)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at org.apache.commons.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208)
at net.hydromatic.optiq.runtime.ResultSetEnumerable.enumerator(ResultSetEnumerable.java:139)
... 17 more
Query2 :
select e.NAME,
CASE WHEN e.dept_ID = (select d.ID from PV_ADMIN.dept d where d.NAME = 'SALES') then 'SALES'
ELSE 'Not Matched.'
END as department
from PV_ADMIN.EMP e ;
ERROR IN OPTIQ 2:
java.sql.SQLException: while executing SQL: select e.NAME,
CASE WHEN e.dept_ID = (select d.ID from PV_ADMIN.dept d where d.NAME = 'SALES') then 'SALES'
ELSE 'Not Matched.'
END as department
from PV_ADMIN.EMP e
at net.hydromatic.avatica.Helper.createException(Helper.java:40)
at net.hydromatic.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:80)
at net.hydromatic.optiq.impl.mongodb.TestOptiq.main(TestOptiq.java:605)
Caused by: java.lang.NullPointerException
at net.hydromatic.optiq.impl.jdbc.JdbcRules.addSelect(JdbcRules.java:76)
at net.hydromatic.optiq.impl.jdbc.JdbcRules.access$1300(JdbcRules.java:50)
at net.hydromatic.optiq.impl.jdbc.JdbcRules$JdbcAggregateRel.implement(JdbcRules.java:556)
at net.hydromatic.optiq.impl.jdbc.JdbcImplementor.visitChild(JdbcImplementor.java:93)
at net.hydromatic.optiq.impl.jdbc.JdbcRules$JdbcJoinRel.implement(JdbcRules.java:242)
at net.hydromatic.optiq.impl.jdbc.JdbcImplementor.visitChild(JdbcImplementor.java:93)
at net.hydromatic.optiq.impl.jdbc.JdbcRules$JdbcProjectRel.implement(JdbcRules.java:426)
at net.hydromatic.optiq.impl.jdbc.JdbcImplementor.visitChild(JdbcImplementor.java:93)
at net.hydromatic.optiq.impl.jdbc.JdbcToEnumerableConverter.generateSql(JdbcToEnumerableConverter.java:271)
at net.hydromatic.optiq.impl.jdbc.JdbcToEnumerableConverter.implement(JdbcToEnumerableConverter.java:81)
at net.hydromatic.optiq.rules.java.EnumerableRelImplementor.implementRoot(EnumerableRelImplementor.java:65)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl$OptiqPreparingStmt.implement(OptiqPrepareImpl.java:699)
at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:276)
at net.hydromatic.optiq.prepare.Prepare.prepareSql(Prepare.java:178)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare2_(OptiqPrepareImpl.java:333)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepare_(OptiqPrepareImpl.java:242)
at net.hydromatic.optiq.prepare.OptiqPrepareImpl.prepareSql(OptiqPrepareImpl.java:211)
at net.hydromatic.optiq.jdbc.OptiqConnectionImpl.parseQuery(OptiqConnectionImpl.java:142)
at net.hydromatic.optiq.jdbc.MetaImpl.prepare(MetaImpl.java:603)
at net.hydromatic.avatica.AvaticaStatement.executeQuery(AvaticaStatement.java:77)
... 1 more
Tables :
Kunal
