You can translate the question and the replies:

DB2 - firstdayofmonth with an Analytical function

Database: DB2 I created a view with a date compare in the where clause ``` field1 >= addmonth(firstdayofmonth(current_date()), -2) ``` Then used an analytical function in the same view ``` row_number() OVER ( PARTITION BY field2, field3 ORDER BY field4 DESC ) ``` When I run this view, I get an error "Finished with error: Error executing view: Function row_number is not executable" The rownumber exits in source configuration->delegate analytical functions lists and it is used in other views. To debug, started removing the functions one at a time in the comparison and found firstdayofmonth is causing this to fail. Currently the view excutes fine with this comparison ``` field1 >= addmonth(current_date(), -2) ``` The firstdayofmonth function is used and works just fine in other views in the same data source. To try things, I included firstdayofmonth in the datasource ->source configuration-> delegate scalar function lists as that was missing and I get "Unable to find mapping for function 'firstdayofmonth'" error when I run the view. Please advice.
DB2
user
09-04-2020 18:14:10 -0400

1 Answer

Hi, The function row_number() is an analytical function. Virtual DataPort can push down this function to a database, but cannot execute it. If a query uses this function and it cannot be pushed down to a database, the query will fail. So if it can't delegate it, either because the source doesn't support it, or for any other reason, you get that error. For more information please refer to [Row Number](https://community.denodo.com/docs/html/browse/7.0/vdp/vql/appendix/syntax_of_condition_functions/analytic_functions_window_functions#row-number) document. So basically the problem is that you have written a query that can’t be delegated because it contains the firstdayofmonth but needs to be delegated because it contains row_number(). This is because DB2 doesn’t support firstdayofmonth function, therefore it can only be executed in Virtual DataPort and not delegated. It could be done if the query was written in such a way where the delegation of row_number() didn’t require the delegation of firstdayofmonth. Hope this helps!
Denodo Team
14-04-2020 15:25:18 -0400
You must sign in to add an answer. If you do not have an account, you can register here