You can translate the question and the replies:

The ROW_NUMBER() function is not executable Error.

The Developer encountered an error with the Analytics function recently, whereas it was working properly before. They ran the following VQL query and faced an error: WITH most_compliance AS( SELECT mlsd_office_id, mlsd_unified_id, AVG(compliance_value) AS compliance_value_avg FROM t_compliance_calc_result WHERE wage_period_id >= 202101 AND wage_period_id < 202401 GROUP BY 1, 2 HAVING MIN(compliance_value) >= 90 AND COUNT(compliance_value) = 36 ), segment AS( SELECT "MLSD_OFFICE_ID" AS office, "MLSD_UNIFIED_ID" AS unified, "REGISTERED_LABORERS", segment_name FROM "T_COMPLIANCE_CALC_SUMMARY" JOIN dim_segment ON "REGISTERED_LABORERS" >= staring_segment AND "REGISTERED_LABORERS" <= ending_segment WHERE "WAGE_PERIOD_ID" = 202312 ), add_segment AS( SELECT most_compliance.*, segment_name FROM most_compliance JOIN segment ON mlsd_office_id = office AND mlsd_unified_id = unified ) SELECT *, ROW_NUMBER() OVER(PARTITION BY segment_name ORDER BY compliance_value_avg DESC) AS est_rank FROM add_segment The issue reported is that the** ROW_NUMBER() function is not executable**. I have checked the source configuration, and the Analytics function is already enabled. Additionally, I contacted the DB team, and they confirmed that the user has the necessary privileges, and no changes have been made from their side. * What is the root cause of this error? * Is the syntax correct? * What should the developer do? * Is there any privilege that should be assigned to the developer at the DB level? Note: t_compliance_calc_result is a Base view dim_segment is a Derived view
user
18-01-2024 06:06:55 -0500
code

1 Answer

Hi, In general, the **row_number()** function, as well as the rest of the [analytical functions](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/functions/analytic_functions/analytic_functions) supported by Denodo, can be [delegated](https://community.denodo.com/docs/html/browse/8.0/en/vdp/administration/creating_data_sources_and_base_views/data_source_configuration_properties/data_source_configuration_properties#:~:text=Delegate%20analytic%20functions%20list) by the Virtual Data Port to a data source, but they cannot be executed in the Virtual layer of Denodo. Thus, when a data source does not support the analytic function, this error is thrown. Also, you have mentioned that the query was working properly before. So, in that case, you could check if you performed any recent changes regarding the data source and also check if the configured data source supports the row_number() function. You could also refer to the [Workaround to Execute Analytic Functions](https://community.denodo.com/docs/html/browse/8.0/en/vdp/vql/functions/analytic_functions/analytic_functions#workaround-to-execute-analytic-functions) documentation for more details. Hope this helps!
Denodo Team
22-01-2024 07:45:35 -0500
code
You must sign in to add an answer. If you do not have an account, you can register here