You can translate the question and the replies:

row_number not working with the get_view_columns

Hello Denodo gurus! Why does row_number not work with the get_view_columns? select database_name, view_name, column_name as denodo_column_name, ROW_NUMBER() OVER ( PARTITION BY view_name ORDER BY database_name ) row_number_with_partition from get_view_columns() The above gives the error: Finished with error: Error executing view: Function row_number is not executable But if I use this query with one of the views that I created, it works fine. Please let me know what might be the issue here?
user
13-02-2020 15:57:58 -0500

1 Answer

Hi, [ROW_NUMBER](https://community.denodo.com/docs/html/browse/latest/vdp/vql/appendix/syntax_of_condition_functions/analytic_functions_window_functions#row-number) is an [analytic (window) function](https://community.denodo.com/docs/html/browse/latest/vdp/vql/appendix/syntax_of_condition_functions/analytic_functions_window_functions#analytic-functions-window-functions). Analytic functions can only be pushed down to a database for execution and cannot be executed in Virtual DataPort, so I only use analytic functions when the underlying database supports their delegation. In your case, [GET_VIEW_COLUMNS](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/stored_procedures/predefined_stored_procedures/get_view_columns#get-view-columns) is a predefined Virtual DataPort stored procedure, and ROW_NUMBER cannot be executed in Virtual DataPort. ROW_NUMBER worked in the other view that you created because it was supported by the underlying data source. If you would still like to use ROW_NUMBER with a predefined stored procedure, you could use [data movement](https://community.denodo.com/docs/html/browse/latest/vdp/vql/appendix/syntax_of_condition_functions/analytic_functions_window_functions#workaround-to-execute-analytic-functions) to move the data at execution time to a database that supports ROW_NUMBER. I was able to implement what you described doing the following steps: 1. Create a view by providing the desired VQL. For example: ``` CREATE VIEW "view1" AS <SELECT statement>; ``` 2. Create another view. This view's VQL should be nearly identical to the first view, but the name of the first view should be specified in the FROM clause instead of GET_VIEW_COLUMNS. In this view, use data movement: ``` CONTEXT(DATAMOVEMENTPLAN = <view name> : JDBC <database>.<data source> ); ``` Make sure the database you are using for data movement supports ROW_NUMBER. Hope this helps!
Denodo Team
14-02-2020 21:44:17 -0500
You must sign in to add an answer. If you do not have an account, you can register here