Are the two columns CS.ProviderID and b.`Value` of the same datatypes in your SQL statement ? ________________________________ From: Sanjiv Kumar <[email protected]> Sent: Friday, January 20, 2017 1:59:35 PM To: [email protected] 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.?*
