You can translate the question and the replies:

Aggregation of derived views delegating search methods to base view

Hello, i have following problem. I want to expose a REST Web Service that shows aggregated data coming from one single Base View. The Base View can be seen as the following having some input parameters (BV has search methods): | ID | Desc | Value | Category | | -------- | -------- | -------- | -------- | | 1 | length | 10 | A | 1 | width | 20 | A | | 1 | battery | 30 | B | | 1 | power | 40 | C The end result needs to be seen as follows: | ID | equipment | technical data | | -------- | -------- | -------- | | 1 | Array | Array | where **equipment** has an Array like the following: | description | value | | -------- | -------- | | power | 20 | | battery | 30 | and **technical data** additional Arrays: | measurements | additional | weights | | -------- | -------- | -------- | | Array | Array | Array | where each array looks the same like table with power and battery. Every Array is created by being filtered by a category. I can create a derived view that has view parameters with default values and test this VQL statement: ``` select nest(description, value, category) as equipment from i_derived where category in ('A,B') union select nest(description, value, category) as technical_data from i_derived where category in ('C'); ``` leads to this: | equipment | technical_data | | -------- | -------- | | Array | null | |null | Array | This could partially solve the **equipment** nest part, but then **technicaldata** needs to be further nested. I have also tried creating one derived view **i_derived** from the BV and then: - one derived view for **equipment** that filters by category and then uses nest to create the array - one derived view for measurements that filters by category and then uses nest to create the array - one derived view for additional that filters by category and then uses nest to create the array - one derived view for weights that filters by category and then uses nest to create the array - one derived view that joins weights, measurements and additional and nests the join as **technical_data** - one final derived view that joins the **equipment** and **technical_data** This causes some problems: - search methods are not "distributed" to the final derived view so they remain static in **i_derived** - the query on the final derived view causes the BV to call the data source four times How would be a good approach to handle this problem? I'm new to VQL and all and this is a little bit confusing without being able to do subqueries in the Select statement or handling search methods. I appreciate the help in advance.

3 Answers

Hi, To create multiple arrays in a single view, I would use the **[NEST](https://community.denodo.com/docs/html/browse/latest/en//vdp/vql/appendix/syntax_of_condition_functions/aggregation_functions#nest)** function like **Nest(field1, field2, ...field N)**. For your scenario, if all the fields in the array are from the same base view, then you could create a single derived view using **[subqueries](https://community.denodo.com/docs/html/browse/8.0/en//vdp/vql/queries_select_statement/from_clause/from_clause#from-clause)** and **[group by clause](https://community.denodo.com/docs/html/browse/8.0/en//vdp/vql/queries_select_statement/group_by_clause/group_by_clause#group-by-clause)** like : > CREATE VIEW <new_view_name> as SELECT equipment, Nest(measurements, additional) as technical_data from ( SELECT Nest(descr, value) as equipment, Nest(<field_name>) as measurements, Nest(<field_name>) as additional from <base_view> group by category ) group by equipment; * You can take a look at the similar community **[Multiple Nest()](https://community.denodo.com/docs/html/browse/8.0/en//vdp/vql/queries_select_statement/group_by_clause/group_by_clause#group-by-clause)** for more information. Hope this helps!
Denodo Team
26-03-2021 08:01:56 -0400
Hello, thanks for the answer. I have made something like this: `SELECT nest(measurements, weights, additional_data) AS technical_data FROM (SELECT nest(v.value, v.description) AS measurements FROM v WHERE category in ('INNENRAUMABMESSUNGEN', 'AUSSENABMESSUNGEN')) INNER JOIN (SELECT nest(v.value, v.description) AS weights FROM v WHERE category = 'GEWICHTE') INNER JOIN (SELECT nest(v.value, v.description) AS additional_data FROM v WHERE category = 'WEITERE DATEN');` This creates the following inside the nest(technical_data) | weights | additional_data | weights | | -------- | -------- | -------- | | Array | Array | Array | as for the equipments nest I did this: `SELECT nest(v.description, v.value) AS equipment, v.token AS token, v.country AS country, v.brand_id AS brand_id, v.model_id AS model_id, v.model_type AS model_type, v.model_year AS model_year, v.color_ext AS color_ext, v.color_int AS color_int FROM v WHERE category in ('FAHRLEISTUNGEN', 'MOTOR, GERIEBE, ELEKTRIK', 'REICHWEITE', 'BATTERIE / LADESYSTEM');` and to achieve this: | equipment | technical_data | | -------- | -------- | | Array | Array | I just joined both views. Now, the problem is this: my Base View has some search methods, which can be obtained from the derived view "v" with the View Parameters that are given default values. I dont know how to propagate those input parameter up to the final derived view so that those parameters are used to call the Rest web services that the Base View uses. The view technical_data cannot be edited afterwards
user
 Edited on: 11-08-2021 05:21:47 -0400
Hi, The **View Parameter** field needs to be specified in the final view, to propagate it from the derived view** "v"**. In your case, you could check whether you have included the** View Parameter** field in your **SELECT** statement. You could specify the SELECT statement like > SELECT nest(v.description, v.value) AS equipment, **<View_parameter_name>**, v.token AS token,….. By specifying the View Parameter field in the query definition, the field gets propagated to the derived view. Additionally, if you still need help and if you are a user with valid support access then you can create a support case in **[Denodo Support Site](https://support.denodo.com/)** so that our support team will assist you further. Hope this helps!
Denodo Team
02-04-2021 07:46:17 -0400
You must sign in to add an answer. If you do not have an account, you can register here