I have a PDI Transformation that is using the Merge rows (diff)
step comparing rows from two Table Input
steps (with Sort
s in between). (I linked a screenshot of the Transformation at the bottom of the post)
The queries in the Table Input
require parameters to be passed, so the options Replace variables in script?
, Insert data from step
, and Execute for each row?
are all active / filled.
With these options disabled / empty and a value hardcoded where the parameter is now defined, the Merge rows (diff)
is working properly and marking the rows with the correct state (new
, changed
, ...).
On the other hand, if I try to run this Transformation with the options active, passing a parameter, the Merge rows (diff)
seems to not be executed - there is a Write to log
step after it and it is not printing anything, opposed to when the options are disabled and the "parameter" is hardcoded.
Has anyone faced a similar issue? Is the Merge rows (diff)
step not implemented to work in this scenario?
Thank you in advance to everyone that might have an idea or suggestion.
Cheers
Screenshot of the simplified PDI Transformation.
Update #1: Add screenshot of Step Metrics
as requested in the comments.
For the entity that was tested, Compare Rows
returns 4 rows, while Reference Rows
does not - this had the desired result of having 4 rows marked as new
by the Merge rows (diff)
step. I will also duplicate this entity to the Reference Rows
table and upload a new screenshot with Step Metrics
where both Table Input
queries return the same rows.
Update #2: Add seconds screenshot of Step Metrics
- where both Table Input
steps output rows (and the same rows). Merge rows (diff)
still does not output anything. Uploading this screenshot I noticed an issue - the Sort
steps were not returning rows, so I replaced them with an ORDER BY
clause in the SQL queries in the Table Input
steps, added the Write to log
steps, and now the Merge rows (diff)
step is getting and outputting rows - even though it should be outputting 4 as identical
instead of the 3 it does, but that is outside the scope of this post.
The "fix" was to remove the Sort row
steps and replace the ordering by an ORDER BY
in the SQL query of the Table Input
steps.