You can translate the question and the replies:

View Parameters and Aggregation

I am fairly new to Denodo and I have read through the Advanced VQL and VDP Administration Guide but I am not able to resolve the following issue: **Sample Base View** For the sake of this issue, let's consider a base with student, months and rank. | STUDENT | MONTH OF YEAR | RANK | | -------- | -------- | -------- | | ABC | 1 | 2 | | ABC | 2 | 4 | | ABC | 3 |1 | | DEF | 1 |1 | | DEF | 3 | 2 | | GHI | 3 | 1 | With the derived view I want to get the highest rank for each student in a given set of months. In T-SQL my query will look something like this: ``` SELECT STUDENT, MAX(RANK) FROM [Sample Base View] GROUP BY STUDENT WHERE [MONTH OF YEAR] IN (1,3) ``` **Result:** | STUDENT | MAX | | -------- | -------- | | ABC | 2 | | DEF | 2 | | GHI | 1 | VQL will look very similar for this setup. However if I want to introduce a parameter in the derived view it introduces a new column in the output corresponding to the view parameter and this throws the grouping completely off when the parameter is used with the IN operator in the WHERE clause. **Derived View: ** ``` CREATE VIEW DerivedView AS SELECT STUDENT, Max(RANK) FROM [Sample Base View] WHERE [Month of Year] IN param GROUP BY STUDENT USING PARAMETERS (param: int) ``` **VQL:** ``` SELECT * FROM DerivedView WHERE param = 1 ``` This will produce the desired results ``` SELECT * FROM DerivedView WHERE param in (1,3) ``` This will **not** produce the desired results. Instead it will further group within each of the param values i.e 1 and 3. So you will end up with two records per student stating the max for each of the months. The same problem happens when using analytical functions like RANK OVER in parameterized derived views. Please keep in mind that the DerivedView will end up being part of a join. So this is not simply a matter of tweaking the SELECT statement that queries the derived view. Also given our enterprise setup, I do not have much access in setting up the VDB or the Denodo instance other than creating datasources, base views and generating derived views - JOINS, INTERSECT, MINUS, UNION etc. Stored Procedures are not an option either.
user
24-02-2017 14:50:23 -0500

4 Answers

Hi, I had run into a similar issue and I followed steps as outlined below: 1. I had used a ‘Split’ function to get a list from the string with all the elements of the ‘IN’ condition. 2. Once this list is created, I had flattened the view. 3. Then created a nested join between this flattened view with my base view (very similar to the one you have). 4. Applied the group by options for all the fields needed (in your case, it could be ‘student’). 5. Applied the filters with the IN operator as string. Instead of param in (1,3), you can give param = ‘1,3’. I got this information from the “Split” section of the advanced VQL guide. You might want to refer this document for some idea. Hope this works for you!
Denodo Team
27-02-2017 08:34:34 -0500
I like that idea. So if I am understanding it right I could construct a view like this one: ``` CREATE VIEW DerivedView FOLDER='/blah/blah' AS SELECT Student, MAX(Rank) FROM [Sample Base View] AS BaseView INNER JOIN ( SELECT string as MonthParam FROM FLATTEN (SELECT SPLIT(',', param)) AS V (V.split)) AS SP ON SP.MonthParam = BaseView.Month GROUP BY Student USING PARAMETERS( param: text ); ``` By the way I am having a little trouble running this particular query in the VQLShell: ``` SELECT string FROM FLATTEN (SELECT SPLIT(',','1,3') AS V) (V.split) ``` Any ideas what I might be doing wrong up there?
user
 Edited on: 28-07-2017 05:35:22 -0400
Hi, I believe this is related to the syntax of the flatten operation. I found some examples in the “Flatten views” section of the advanced VQL Guide and made use of the same. For instance, I created a view with the split function (say view1) and then applied the flatten operation on top of it. You could do something similar to this - FLATTEN (view1 AS v, v.split). Then you can proceed to create the nested join between this flattened view and your base view. Hope this helps.
Denodo Team
02-03-2017 05:50:53 -0500
Thanks. That helped. For the benefit of anyone else that may be trying this out, here is what I had to do: **View to Split the Comma Delimited Parameters** ``` CREATE VIEW split_comma_separated_params FOLDER = '/somefolder' AS SELECT split(',', params), params FROM dual() USING PARAMETERS ( params : text); ``` **Flatten Array Returned from Split View** ``` CREATE VIEW flatten_split_comma_separated_params FOLDER = '/someotherfolder' AS SELECT string AS value, flatten_param FROM FLATTEN split_comma_separated_params AS v ( v.split) WHERE v.params = flatten_param USING PARAMETERS ( flatten_param : text); ``` **Finally Use the Comma Delimited Parameters in a View** ``` CREATE VIEW DerivedView FOLDER='/blah/blah' AS SELECT Student, MAX(Rank) FROM [Sample Base View] AS BaseView INNER JOIN ( SELECT * FROM flatten_split_comma_separated_params ) AS SP ON SP.value = BaseView.Month WHERE SP.flatten_param = param GROUP BY Student USING PARAMETERS( param: text ) ```
user
 Edited on: 28-07-2017 05:35:22 -0400
You must sign in to add an answer. If you do not have an account, you can register here