You can translate the question and the replies:

No delegation cause: "The function 'case' cannot be delegated to this database"

Dear Denodo team, i created a view using a single table from a datasource (no joins). It is basically aggregating, filtering by a where condition, as well as doing some minor transformations and functions (cast, to timestamp, trim, case, now). The filter as well as the trim are pushed down to the datasource, the case and "to timestamp" however are not. The No delegation cause is: "The function 'case' cannot be delegated to this database". The main problem is, that due to not beeing able to push down basic functinos, the whole aggregation groupby is done in the VDP. These are the projected fields: client_id AS client_id customer_id AS customer_id gln_number AS gln_number ean_id AS ean_id to_timestamp('yyyyMMdd', cast(kfmbw_date AS VARCHAR(14))) AS synch_date booking_type AS booking_type kfmbw_bezugsnr AS reference_number document_number AS document_number fmbw_saison AS season fmbw_saison_x7 AS season_name quantity AS quantity to_timestamp('yyyyMMdd', fmbw_buchungsdatum) AS booking_date to_timestamp('yyyyMMdd', fmbw_bewertungsdatum) AS assessment_date business_type AS business_type business_model AS business_model fmbw_ekpreis AS purchase_price retail_price AS retail_price fmbw_vkwaehrung AS retail_currency fmbw_ekpreis_fw AS purchase_price_foreign_currency fmbw_ekwaehrung_fw AS purchase_foreign_currency_type fmbw_vkpreis_fw AS retail_price_foreign_currency fmbw_vkwaehrung_fw AS retail_foreign_currency_ptype to_timestamp('yyyyMMddHHmmss', cast("current_date" AS VARCHAR(14))) AS created_date fmbw_kzansynchuebertragen AS transfered_synch_mark fmbw_kzstorno AS storno_mark fmbw_uvp AS rrp fmbw_uvp_fw AS rrp_foreign_currency case booking_type WHEN 'AB' THEN quantity WHEN 'ABG' THEN quantity ELSE 0 END AS sales_quantity case WHEN (booking_type = 'RT') THEN quantity ELSE 0 END AS return_quantity case WHEN ((booking_type = 'LS' AND business_type = 'V')) THEN quantity ELSE 0 END AS we_order_quantity case WHEN ((booking_type = 'LS' AND business_type = 'N')) THEN quantity ELSE 0 END AS we_stock_quantity case WHEN (booking_type in ('AB', 'ABG', 'KA', 'KO', 'RT', 'UM')) THEN (-1*quantity) ELSE case WHEN (booking_type in ('LB', 'AE')) THEN 0 ELSE quantity END END AS inventory_change (case WHEN (booking_type in ('AB', 'ABG', 'KA', 'KO', 'RT', 'UM')) THEN (-1*quantity) ELSE case WHEN (booking_type in ('LB', 'AE')) THEN 0 ELSE quantity END END*retail_price) AS retail_price_change (case WHEN (booking_type in ('AB', 'ABG', 'KA', 'KO', 'RT', 'UM')) THEN (-1*quantity) ELSE case WHEN (booking_type in ('LB', 'AE')) THEN 0 ELSE quantity END END*fmbw_ekpreis) AS purchase_price_change (case WHEN (booking_type in ('AB', 'ABG', 'KA', 'KO', 'RT', 'UM')) THEN (-1*quantity) ELSE case WHEN (booking_type in ('LB', 'AE')) THEN 0 ELSE quantity END END*fmbw_ekpreis_fw) AS purchase_price_foreign_currency_change (case WHEN (booking_type in ('AB', 'ABG', 'KA', 'KO', 'RT', 'UM')) THEN (-1*quantity) ELSE case WHEN (booking_type in ('LB', 'AE')) THEN 0 ELSE quantity END END*fmbw_vkpreis_fw) AS retail_price_foreign_currency_change (fmbw_uvp-retail_price) AS discount (fmbw_uvp_fw-fmbw_vkpreis_fw) AS discount_foreign_currency sys_loaddate AS sys_loaddate sys_datasource AS sys_datasource The SQL sentence for the datasource however is: SELECT t0.kfmbw_kundennr, t0.kfmbw_ean, t0.kfmbw_gln, t0.fmbw_menge, t0.fmbw_ekpreis, t0.fmbw_bewertungsdatum, trim(t0.kfmbw_buchungsart), t0.fmbw_geschaeftsart, t0.fmbw_vkpreis, t0.fmbw_uvp FROM admin.fmbw t0 WHERE t0.fmbw_saison IN ('V') AND trim(t0.kfmbw_buchungsart) NOT IN ('LB', 'AE') What could be preventing most of the functions top be pushed down? Please let me know, if you need any more information. Best regards, David
user
01-04-2022 03:05:16 -0400
code

2 Answers

Hi, I was able to recreate your scenario by using an Oracle base view with default ‘Embedded Derby Server’ as the cache database. It resulted in ‘No delegation cause - ‘The function ‘case’ cannot be delegated to this database’. Then, I was able to push down the **CASE statement** to the underlying Oracle data source by disabling the cache (set **cache** mode to ‘**Off**’). If I want to enable cache and push down the CASE statement to the cache data source, I would configure an **external relational JDBC database** as the cache database, provided the database supports CASE statement. If you still need help and if you are a valid Support user, then please raise a Support case at [Denodo Support Site](https://support.denodo.com/) so that our Support Team will help you further. Hope this helps!
Denodo Team
01-04-2022 09:15:52 -0400
code
Hello again, thanks for the reply. We realized, that it is actually a driver issue. So not much, we can do about it. Caching the data to a MS SQL Database enabled us to push down the functions. Best regards, David
user
24-05-2022 11:29:01 -0400
You must sign in to add an answer. If you do not have an account, you can register here