0

Suppose I had a query that would return something like this, except 1000 of records. lets call this EncounterTable

EncId | StartDate | EndDate | Weight
1              1/1/2022              1/4/2022             .5
2              12/28/2021         1/14/2022             4.6
3              1/4/2022             1/6/2022             1.7
N             X/X/20XX             Y/Y/20YY              x.Y

What I want to do is transform each encounter row into multiple rows where their StartDate and EndDate are between two dates, such as 1/1/2022 and 1/7/2023. So the first encounter goes from 1/1/2022 - 1/4/2023 would get translated into 4 rows like this: 

Enc1,    1/1/2022, .5
Enc1,    1/2/2022, .5
Enc1,    1/3/2022, .5
Enc1,    1/4/2022, .5

Encounter 2 goes from 12/28/2021-1/14/2022 so it would get transformed into this:

Enc2,    1/1/2022, 4.6
Enc2,    1/2/2022, 4.6
Enc2,    1/3/2022, 4.6
Enc2,    1/4/2022, 4.6
Enc2,    1/5/2022, 4.6
Enc2,    1/6/2022, 4.6
Enc2,    1/7/2022, 4.6

I am new to Denodo VQL. I have a pretty good TSQL background. If I was writing TSQL, I would:

Create a Date temp table with 7 rows 1/1/2022 to 1/7/2022. Maybe I call it DateTempTable

I then would join EncounterTable against DateTempTable where the encounter StartDate/EndDate is between DateTempTable

I do not want to hardcode the values in DateTempTable. Ultimately, the 2 dates would be parameters and I would loop on the parameters to fill in the rows in DateTempTable.

It is my understanding that VQL does not support looping. I find a support question that suggested using Java code and Denodo4E.

I am not very experienced with Denodo and VQL so maybe I am making this overly complicated and that is why I am reaching out. My gut says I am overlooking something very simple!

How would you solve this? Thanks, Dan

DapperDanh
  • 555
  • 2
  • 5
  • 17

1 Answers1

0

It's my understanding you're looking for looping constructs like TSQL in VQL, but VQL does not support explicit looping constructs. However, you can achieve the desired result by leveraging Denodo's features and capabilities. Instead of using a temporary table and looping, you can utilize Denodo's built-in functions and join conditions to achieve the transformation.

VQL stored procedures in Denodo allow users to define custom operations and logic, enhancing data virtualization capabilities for advanced transformations and data manipulations. For further information and guidance on using stored procedures in VQL, you can refer to this link: Use of Stored Procedures — VQL Guide (denodo.com).

Hope this helps.

bklein
  • 26
  • 2