You can translate the question and the replies:

Complex Iterative Queries using "Cursor" or "Loops"

Good morning Denodo Community, I'm trying to build a fairly complex query to calculate values based on a hierarchy. There are N number of nodes which need to generate a value. |-- District 1 (100%) |- Division 1 (60%) ----| | Company (100%) --| | |- District 2 (50%) |- Divison 2 (40%) ----|- Distrcit 3 (25%) |- District 4 (25%) In the above hiearchy, i would need to calculate expenses as a Percent of parent node expense for each child node. For example: I would calculate 100 dollars of expanse like this... Company = 100. Company 100 Division 1 = 60 District 1 = 60 Division 2 = 40 District 2 = 20 District 3 = 10 District 4 = 10 NOTE: this is a simple example. Our real scenario would have N number of nodes.
Cursors Loops Do While
user
31-08-2017 10:54:58 -0400
code

2 Answers

Hi If I need to implement a Cursor to calculate values based on a company hierarchy, I would create a stored procedure in a Relational database and then call it in Virtual DataPort. To do that follow the below steps, * Connect to the Relational database by creating JDBC data source. * Create Base view, by choosing the created stored procedure in the data source. * Execute the created base view in the FROM clause of SELECT query. ``` Select * from view ``` You could refer the section “[Importing Graphically Stored Procedures from a Database](https://community.denodo.com/docs/html/browse/6.0/vdp/administration/creating_views/importing_data_sources_and_creating_base_views/jdbc_sources#importing-graphically-stored-procedures-from-a-database)” in Virtual DataPort Administration Guide for more details on importing Stored Procedures in Virtual DataPort. Hope this helps!
Denodo Team
01-09-2017 10:20:44 -0400
code
Thanks for the reply. In this case, i was hoping to leverage the power of virtualization since the data resides in different systems. In this particular case, expenses reside in an accounting system and the rules managed in a different application. Cursors and Loops are pretty common query patterns. How do we recommend adding this type of feature?
user
01-09-2017 10:28:07 -0400
You must sign in to add an answer. If you do not have an account, you can register here