How to use CASE in Apache Drill

1
votes

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.

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.?

1
apache-drill

1 Answers

0
votes

In Apache-drill you cannot use select statement inside case. In fact you cannot use inner select statement for a column at all.

Try taking the inner select as a separate table and join with main table, that should work.