Hello Rudi,Have you tried using an additional upstream transformations to determine the values to use in the output port?The Integration Service should be able to generate an SQL statement for an expression that does use output ports from upstream transformations because it can then determine the values to use.Best regards,Syed
2 of 2 people found this helpful
If you want to have this logic with PDO you must change the approach.
In this case I would read the source twice, once the product column and the order column and perform an aggregation on the order (group by product)
After the aggregator join on both product and order column on the data with all the columns (inner join)
Check also KB article 109671 about not supported transformations and functions
Hope this helps
I'm wondering why I didn't think of this solution
PDO offers you a lot of opportunities, not only in performance but also in building mappings.
Would you ever imagine building a mapping with source/source qualifier and then a filter transformation?
(except on flatfiles of course).
But if you do PowerCenter will treat the filter transformation as source filter when you're using PDO.
So you have all the functions etc available but you don't need to worry about syntax, PowerCenter will do at runtime when creating the query.
It's also very convenient when switching RDBMS. I have experienced switching from Oracle to SQL-server but also SQL-server to AWS Redshift.