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.
+16- 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 :
+1francoissimard9 wrote:
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 :
francoissimard9 wrote:
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 :
+1Is there any better approach than showing the attached image, keeping in mind of performance issue using the order by in sql query?
betterone.png
+1francoissimard9 wrote:
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.
david_r wrote:
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.
+1itay wrote:
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
Reply
Rich Text Editor, editor1
Editor toolbars
Press ALT 0 for help
Helpful Members This Week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.