You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@drill.apache.org by Sanjiv Kumar <sa...@gmail.com> on 2017/01/20 08:29:35 UTC

How to use CASE in apache drill

I Have one doubt regarding CASE in Apache Drill.

I am trying to use CASE in latest version of drill (1.9) in Window system &
in embedded mode.

SEE MY QUERY:-

SELECT
CASE WHEN
EXISTS
(SELECT distinct CS.ProviderID
 FROM <pluginName>.<dbName>.<schemaName>.<tableName1> AS `CS`
INNER JOIN <pluginName>.<dbName>.<schemaName>.<tableName2> AS `PS`
ON PS.ID = CS.ProviderID
where CS.PluginName = 'some text from db' AND
PS.Code = 'SQLSERVER' AND CS.AccessKey = 'B2S1' limit 1)
THEN
(SELECT distinct b.`Value` from
<pluginName>.<dbName>.<schemaName>.<tableName3> AS a
inner join <pluginName>.<dbName>.<schemaName>.<tableName4> as b
 on a.ID = b.PermissionID
inner join <pluginName>.<dbName>.<schemaName>.<tableName5> as c
 on c.RoleID=b.RoleID and c.ModuleID=1 limit 1)
ELSE 0
END FROM  <pluginName>.<dbName>.<schemaName>.<tableName3>;


I am trying to execute this query but it throughing error.

SEE ERROR:-

org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR:
From line 2, column 1 to line 7, column 4: Illegal mixing of types in CASE
or COALESCE statement SQL Query null

*What i am doing wrong in this query.?*

Support for Kafka

Posted by Chetan Kothari <ch...@oracle.com>.
Is there any support to query kakfa from Drill?

If not, what is the roadmap/plan to support this feature?

 

Regards

Chetan

Re: How to use CASE in apache drill

Posted by Khurram Faraaz <kf...@mapr.com>.
Are the two columns CS.ProviderID and b.`Value` of the same datatypes in your SQL statement ?
________________________________
From: Sanjiv Kumar <sa...@gmail.com>
Sent: Friday, January 20, 2017 1:59:35 PM
To: user@drill.apache.org
Subject: How to use CASE in apache drill

I Have one doubt regarding CASE in Apache Drill.

I am trying to use CASE in latest version of drill (1.9) in Window system &
in embedded mode.

SEE MY QUERY:-

SELECT
CASE WHEN
EXISTS
(SELECT distinct CS.ProviderID
 FROM <pluginName>.<dbName>.<schemaName>.<tableName1> AS `CS`
INNER JOIN <pluginName>.<dbName>.<schemaName>.<tableName2> AS `PS`
ON PS.ID = CS.ProviderID
where CS.PluginName = 'some text from db' AND
PS.Code = 'SQLSERVER' AND CS.AccessKey = 'B2S1' limit 1)
THEN
(SELECT distinct b.`Value` from
<pluginName>.<dbName>.<schemaName>.<tableName3> AS a
inner join <pluginName>.<dbName>.<schemaName>.<tableName4> as b
 on a.ID = b.PermissionID
inner join <pluginName>.<dbName>.<schemaName>.<tableName5> as c
 on c.RoleID=b.RoleID and c.ModuleID=1 limit 1)
ELSE 0
END FROM  <pluginName>.<dbName>.<schemaName>.<tableName3>;


I am trying to execute this query but it throughing error.

SEE ERROR:-

org.apache.drill.common.exceptions.UserRemoteException: VALIDATION ERROR:
From line 2, column 1 to line 7, column 4: Illegal mixing of types in CASE
or COALESCE statement SQL Query null

*What i am doing wrong in this query.?*