You can translate the question and the replies:

selection queries cannot be delegated to the database using case clause from Hive

I have used full join between two tables and added the new column with sample case statement as CASE WHEN A IS NOT NULL THEN B WHEN C IS NOT NULL THEN D ELSE NULL end. If I see the query plan showing warning as selection queries cannot be delegated to the database but am able to fetch the data during execution. I have enabled the source configuration properties delegate inner join,join and natual outer join to YES. Any reasons why case clause cannot be delegated to the database?Also will these warnings will have any impact with respect to data which fetching it from source? If I use inner join am not getting the warning selection queries cannot be delegated but giving error as "method not supported" while running the view.Looks like NULL not supported. By removing the null from case clause then the inner join is working. Please help me how case clause can be resolved with respect to delegations from full join and inner join with NULL.
Denodo and Hadoop
user
22-02-2018 07:27:06 -0500
code

3 Answers

Hi, The warning message “Projection/Selection queries cannot be delegated to this database” means that the Virtual DataPort will be responsible of performing the operations that can not be delegated to the source. This could happen when you have created a base view using a SQL query and you are querying this view using additional conditions that cannot be delegated to the database. I have executed a join view that has a field with the case statement as you mentioned. I didn't receive any error or warning message. When I checked the execution trace, the case clause is delegated to the Hive data source. You could check if you have added any condition or functions that is not supported by the Hive Data Source and also check if the property "Delegate SQL sentence as subquery property" is set to "Yes". You could find this property by navigating to "Search Methods > Wrapper Source Configuration" of the base view. You can have a look at[ Does creating a base view from query mean the query can not be delegated?](https://community.denodo.com/kb/view/document/Does%20creating%20a%20base%20view%20from%20query%20mean%20the%20query%20can%20not%20be%20delegated%3F?category=Data+Sources) Knowledge Base article for futher information. Hope this helps!
Denodo Team
23-02-2018 07:23:55 -0500
code
Hi. Thanks for your reply. I have used case clause in the derived view with new field but not with base view with SQL Query. CASE WHEN A IS NOT NULL THEN B WHEN C IS NOT NULL THEN D ELSE NULL end When I run the derived view getting error as method not supported. Below is the sql query of case clause from the execution trace where null is transformed to ? and hive not supporting. SELECT t1.a, t1.b, t1.c, t1.d, t1.e, t1.dt, CASE WHEN t1.a IS NOT NULL THEN t1.b WHEN t1.c IS NOT NULL THEN t1.d ELSE ? END FROM ( SELECT max(t0.dt) AS dt FROM <table> t0) s0, <table> t1 WHERE (s0.dt = t1.dt) parameterts:[null] So I changed the case clause for temporary as: CASE WHEN A IS NOT NULL THEN B WHEN C IS NOT NULL THEN D ELSE '9999-12-31' end When I run the derived view and it ran successfully. I have checked the property “Delegate SQL sentence as subquery property” and is set to “Yes” in the wrapper source configuration. With respect to queries cannot be delegated to the database I have used the logic as : case WHEN (to_date('yyyy-MM-dd', substring(aum_end_dt, 0, 10)) < now()) THEN 'EXPIRED' ELSE 'ACTIVE' END The execution trace shows to_date cannot be delegated to the database. Can you please help me how to resolve the issue with NULL as the method is not supporting and to_date as the function not delegated to the database.
user
26-02-2018 07:39:18 -0500
Dear Support Team, Can you pls reply to my query. Hi. Thanks for your reply. I have used case clause in the derived view with new field but not with base view with SQL Query. CASE WHEN A IS NOT NULL THEN B WHEN C IS NOT NULL THEN D ELSE NULL end When I run the derived view getting error as method not supported. Below is the sql query of case clause from the execution trace where null is transformed to ? and hive not supporting. SELECT t1.a, t1.b, t1.c, t1.d, t1.e, t1.dt, CASE WHEN t1.a IS NOT NULL THEN t1.b WHEN t1.c IS NOT NULL THEN t1.d ELSE ? END FROM ( SELECT max(t0.dt) AS dt FROM <table> t0) s0, <table> t1 WHERE (s0.dt = t1.dt) parameterts:[null] So I changed the case clause for temporary as: CASE WHEN A IS NOT NULL THEN B WHEN C IS NOT NULL THEN D ELSE '9999-12-31' end When I run the derived view and it ran successfully. I have checked the property “Delegate SQL sentence as subquery property” and is set to “Yes” in the wrapper source configuration. With respect to queries cannot be delegated to the database I have used the logic as : case WHEN (to_date('yyyy-MM-dd', substring(aum_end_dt, 0, 10)) < now()) THEN 'EXPIRED' ELSE 'ACTIVE' END The execution trace shows to_date cannot be delegated to the database. Can you please help me how to resolve the issue with NULL as the method is not supporting and to_date as the function not delegated to the database.
user
02-03-2018 07:34:32 -0500
You must sign in to add an answer. If you do not have an account, you can register here