OCI Data Integration - Filter using row_number() over in Data Flow
I'm hoping this is something obvious that I'm overlooking. What I'm trying to achieve is filtering a data source (ADW table) by partition and only keeping the top row for each one.
So far I have added the data source, created a filter and used the analytic function "row_number() over(..." with a filter condition of that = 1. However this throws an error when running stating "ORA-30483: window functions are not allowed here".
This is easily achieved using a cte query in sql, but I'm struggling to get it working the same way in OCI Data Integration.
Is anyone able to point me in the right direction?