[CALCITE-259] Using sub-queries in CASE statement against JDBC tables generates invalid Oracle SQL

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Fix Version/s: 1.3.0-incubating
• Labels: github-import
• Assignee: Julian Hyde
• Reporter: GitHub Import
• Created: 18/Jun/14 08:52
• Updated: 01/Jun/15 18:25
• Resolved: 21/May/15 19:54

Description

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
Labels: duplicate,
Created at: Fri Apr 25 06:41:04 CEST 2014
State: open