Hi,
I have a question regarding how to perform an operation in Denodo.
I have data in a base view (on a Snowflake data source) that looks like:
| Order_number | Icd10_codes | Icd9_codes |
| -------- | -------- | -------- |
| 1001 | 100.1,101.2 | |
| 1002 | | 98.4Z |
I want to create a view with the resulting output being:
| Order_number | Row_num | Icd_code |
| 1001 | 1 | 100.1 |
| 1001 | 2 | 101.2 |
| 1002 | 1 | 98.4Z |
So with the original icd10_codes and icd9_codes strings split on the ',' character then flattened and the item number of the split string for each order_number as row_num.
So far what I have done is create two views.
View1 splits and unions icd10_codes and icd9_codes:
Select order_number, split(',',Icd10_code) as icd
UNION
Select order_number, split(',',Icd9_code) as icd
The second view (view2) flattens the output of this view:
Select
Order_number,
row_number() OVER(PARTITION BY Order_number ORDER BY Order_number) as row_num, <- this does not work
String as Icd_code
FROM FLATTEN view1 AS v ( v.icd)
It all works except for the row_number, as that gives me a resulting message of "Finished with error: Error executing view: Function row_number is not executable".
I am guessing this is because since this view is based on another derived view, it won't delegate row_number() down to Snowflake. I checked and row_number() is one of the analytic functions delegated in the Snowflake data source. How would I achieve my desired output with a row_number for each row per order_number?
