Question

Get sorted record using a transformer

  • 27 September 2016
  • 9 replies
  • 13 views

Badge +1

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.


9 replies

Badge +16

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 :

Badge +1

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 :

Hi @francoissimard9, If we sort child table with many records for one parent record before FeatureMerger, I should not get multiple records after merged for single parent record.

 

 

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 :

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.

 

 

Badge +1

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

Badge +1
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.

 

 

Yes @ francoissimard9, featuremerger is giving one record that is the first record with out sort order by Seq No, so the value is correct. instead i used featurereader with order by , i got the correct one record but performance problem while handling million of records.Please see the my answer at betterone.png, need better approach.

 

 

Userlevel 5

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.

I completely agree. I always perform my joins in the database before sending them to my translation when reading database formats, it is indeed much faster than loading them in FME and join them there.

 

Badge +1

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

Using Joiner with prefix query like below and Cordinality First Match will also get the result with out duplicate data but sorted. But performance is slow. SELECT * from dbo.[Dept] ORDER BY [SeqNo] ASC

 

Reply