In Denodo (version 9.0.10), I have a BaseView called "bv_AuditBase" containing two very important columns: "AttributeMask" and "ChangeData." The columns are filled in according to this rule:
- "AttributeMask" always begins and ends with a "," (comma). The comma in this column acts as a value separator, but it's poorly organized because there are no corresponding values before the initial "," (comma) and after the final ",". Therefore, the values to be considered are only those with a "," character both before and after them.
- "ChangeData," on the other hand, uses the tilde character to separate values. In this column, unlike the previous one, there is no initial or final tilde, so values always match before and after it (even missing values, for which it is possible to find two consecutive tildes, indicating that there is a missing value in the middle).
For example:
| AttributeMask | ChangeDate |
|---|---|
| ,203,360,42,10049,45 | ~~~100~ |
| ,10049, | 101 |
| ,10049,6, | 104~ |
| ,5,18,10049,3 | 30~31~100~ |
The values in the two columns are linked, so the first value in the first row of "AttributeMask" is linked to the first value in the first row of "ChabgeData," the second value in the first row of "AttributeMask" is linked to the second value in the first row of "ChangeData" and so on.
I have a specific value I'm interested in in the "AttributeMask" column, which corresponds to the value "10049." This value is actually a code that indicates when a work order has changed from one status to another (for example, from "Accepted" to "Completed"). I get the work order status code from the "ChangeData" column, so for each value in the "AttributeMask" rows that equals "10049," I must also save the corresponding values in the "ChangeData" column.
The resulting table should have, for each row, the value "10049" from the "AttributeMask" column and the corresponding value from the "ChangeData" column. However, I need to do this using Denodo Design Studio (Version 9.0.10). Could you help me write code that allows me to do this using VQL?
