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)
| 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
SELECT STUDENT, Max(RANK)
FROM [Sample Base View]
WHERE [Month of Year] IN param
GROUP BY STUDENT
USING PARAMETERS (param: int)
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.