[
https://issues.apache.org/jira/browse/CALCITE-259?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14558011#comment-14558011
]
YeongWei commented on CALCITE-259:
----------------------------------
Hi [~julianhyde]
Appreciate with the commit link shared!
I have notice your implementation for the HSQLDB scenario. You have chosen to
use the CASE Statement for the SINGLE_VALUE implementation.
On a side note, I have opened a ticket with HSQLDB community, this can be seen
from the link below,
https://sourceforge.net/p/hsqldb/bugs/1396/
It is related to the CASE Statement return value's data types. It appears that
for the CASE Statement in HSQLDB the return type has to be consistent. It
appears to me that embedding the Scalar-Subquery into a Simple Select would be
easier.
Let me know what do you think.
Thanks!
> Using sub-queries in CASE statement against JDBC tables generates invalid
> Oracle SQL
> ------------------------------------------------------------------------------------
>
> Key: CALCITE-259
> URL: https://issues.apache.org/jira/browse/CALCITE-259
> Project: Calcite
> Issue Type: Bug
> Reporter: GitHub Import
> Assignee: Julian Hyde
> Labels: github-import
> Fix For: 1.3.0-incubating
>
> Attachments: CALCITE-259-MYSQL-SINGLE_VALUE-0.1.patch,
> [CALCITE-259]-Description.patch, [CALCITE-259]-Description.patch
>
>
> 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;
>
> 2.
> 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 ;
> ---------------- Imported from GitHub ----------------
> Url: https://github.com/julianhyde/optiq/issues/259
> Created by: [kunal-mahale|https://github.com/kunal-mahale]
> Labels: duplicate,
> Created at: Fri Apr 25 06:41:04 CEST 2014
> State: open
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
