Hi @christian_b, if you saved all the attributes into a list attribute before executing the DELETE statement, you can restore the input features with the ListExploder later. The Aggregator (Group By: value, Mode: Geometry - Assemble One Level) with the Generate List option can also be used instead of the ListBuilder in this screenshot.
Alternatively, this workflow is possible. Note: The Connection Runtime Order at the Unique port of the DuplicateFiter has to be set to run the upper AttributeCreator first. See here to learn more about Connection Runtime Order:
Controlling Transformer Flow at Runtime
Hope this helps.
Hi @christian_b, if you saved all the attributes into a list attribute before executing the DELETE statement, you can restore the input features with the ListExploder later. The Aggregator (Group By: value, Mode: Geometry - Assemble One Level) with the Generate List option can also be used instead of the ListBuilder in this screenshot.
Alternatively, this workflow is possible. Note: The Connection Runtime Order at the Unique port of the DuplicateFiter has to be set to run the upper AttributeCreator first. See here to learn more about Connection Runtime Order:
Controlling Transformer Flow at Runtime
Hope this helps.
The DatabaseDeleter could be another option.
Hi Takashi, thank you very much for this very accurate answer.
I found a solution this morning by using a FeatureMerger transformer behind my SQLExecutor and it works but I think it's not very elegant...
Your solution seems far better and I will give it a try.
About your second answer with the DatabaseDeleter transformer, I don't think it would work because the delete operation has to be done before the insert operation and in your graph both operations are working in parallel.
Have a nice day !
Christian
@christian_b
calling the sql executer for each value is not efficient at all.
Passing a list trough is not efficient either. (unless list is small )
I would go for @takashi's last version and replace the database deleter with a sql executor performing the delete with an "IN" statement (you can hold quite a lot of value's in a string). "IN" string created on attribute_value with a concatenator or attribute creator.
Call the sql executer with a sample of 1.
After that you can execute the writer.
@christian_b
calling the sql executer for each value is not efficient at all.
Passing a list trough is not efficient either. (unless list is small )
I would go for @takashi's last version and replace the database deleter with a sql executor performing the delete with an "IN" statement (you can hold quite a lot of value's in a string). "IN" string created on attribute_value with a concatenator or attribute creator.
Call the sql executer with a sample of 1.
After that you can execute the writer.
Hi gio, thank you for this answer.
Some points you mention seem very interesting but I don't really understand how to achieve your solution.
Is this your procedure :
1- DuplicateFilter
2- Create "IN" attribute with concatenator or attributecreator that contains the distinct filtered values
3- SQL Executor like "Delete from my_table where code IN @value(my_in_attribute)"
--> what do you mean with "sample of 1" ?
4- Execute the writer: but how to do this after my SQLExecutor that returns only the filtered values ?
@christian_b
Create the In string by concatenation.
Listbuilder (grouped by feature type), listduplicateremover on the value attribute (or a duplicate filter prior to listbuilder).
By "sample of 1" I mean only use one feature to trigger the sql-executor. (if u listbuild on the sheet you probably end up with just one feature anyway)
You can execute the writer by (for instance).
Create a "trigger" attribute after the "deleter" SQL-executor. Use the fme_feature type name.
Link/relate/merge this to the features to be written unconditionally (variable setter/retriever could also possible). Which needs the fme_feature_type exposed on the sheet.
Then test if the requestor and the supplier have same trigger value. Merged go to the writer.
@christian_b
Create the In string by concatenation.
Listbuilder (grouped by feature type), listduplicateremover on the value attribute (or a duplicate filter prior to listbuilder).
By "sample of 1" I mean only use one feature to trigger the sql-executor. (if u listbuild on the sheet you probably end up with just one feature anyway)
You can execute the writer by (for instance).
Create a "trigger" attribute after the "deleter" SQL-executor. Use the fme_feature type name.
Link/relate/merge this to the features to be written unconditionally (variable setter/retriever could also possible). Which needs the fme_feature_type exposed on the sheet.
Then test if the requestor and the supplier have same trigger value. Merged go to the writer.
Thanks gio for the precision.
I think I understand the process, but I'm not yet comfortable enough with FME.
I will give a try to this solution to discover another aspects of FME.
