Do we have a transformer which which joins two parent and child table using common key. where child has many references for each parent key, child table has one Sequence number field to sort or order by to get the first row. Like we get ORDER BY Seq_NO and ROWNUM=1 in sql query.
Question
Get sorted record using a transformer
This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.
+18- Supporter
Hi @natraju Have a look at the Joiner especially if you are using a database format.
For optimizing the joiner features (sorting) see the optimize parameter of the transformer.
Hope this helps
If your goal is to join only the first record of your "child" table into your parent table, I would use a Sorter and a FeatureMerger to join those 2 tables. The Sorter would sort your features based on your sequence attribute, then you connect it to the supplier port of the FeatureMerger. Set the parameter for "process duplicate suppliers" to "no", and the FeatureMerger will join only the first record it finds, and the others will be output via the DuplicateSupplier port :
+4- Contributor
If your goal is to join only the first record of your "child" table into your parent table, I would use a Sorter and a FeatureMerger to join those 2 tables. The Sorter would sort your features based on your sequence attribute, then you connect it to the supplier port of the FeatureMerger. Set the parameter for "process duplicate suppliers" to "no", and the FeatureMerger will join only the first record it finds, and the others will be output via the DuplicateSupplier port :
If your goal is to join only the first record of your "child" table into your parent table, I would use a Sorter and a FeatureMerger to join those 2 tables. The Sorter would sort your features based on your sequence attribute, then you connect it to the supplier port of the FeatureMerger. Set the parameter for "process duplicate suppliers" to "no", and the FeatureMerger will join only the first record it finds, and the others will be output via the DuplicateSupplier port :
+4- Contributor
Is there any better approach than showing the attached image, keeping in mind of performance issue using the order by in sql query?
betterone.png
+4- Contributor
Hi @natraju, in this situation, the Feature Merger will only merge the first record it finds, so the Merged port will output one record for each single parent record that has found a match in the child table.
If the parent and the child table both reside in the same database, the most efficient is to join them directly in the database using either a SQLCreator or a SQLExecutor.
Loading huge numbers into FME and sorting/filtering them later is always going to be a lot slower than letting the database do the work for you before loading it into FME.
The Joiner, as suggested by Itay could also be a good contender for some scenarios, particularly when you might re-use a lot of child records since the Joiner will cache results in memory.
If the parent and the child table both reside in the same database, the most efficient is to join them directly in the database using either a SQLCreator or a SQLExecutor.
Loading huge numbers into FME and sorting/filtering them later is always going to be a lot slower than letting the database do the work for you before loading it into FME.
The Joiner, as suggested by Itay could also be a good contender for some scenarios, particularly when you might re-use a lot of child records since the Joiner will cache results in memory.
+4- Contributor
Hi @natraju Have a look at the Joiner especially if you are using a database format.
For optimizing the joiner features (sorting) see the optimize parameter of the transformer.
Hope this helps