I would like to create a generic job, in which I have to convert the fields before wrtiing them to out target database.
Therefore, I splitted the table into 3: one having only fields which should be converted to text, one with all numeric fields and one with timestamps. I would like to convert all fields and put them back into the original table.
For the first run with one target table, there is no problem, as the fields are always the same. As I filter my “relevant” fields before, it would be a solution to have the possibility to use the “convert data type”-action with the selection “ALL FIELDS” instead of explicit selection.
Is there a way to have this selection in the action or has someone another solution fort that?
There are 2 generic approaches for applying the same calculation to all fields:
Unpivoting / repivoting
In this case you unpivot the table so that all columns become 1 column (this may produce a very long table). Then you perform the calculation and pivot it back.
Pros: simple method
Cons: doesn’t work on large tables because unpivoting can produce a too long table, restoring column order requires an additional workaround (appending to an empty table)
The “Iterate columns” action.
The “Iterate columns” action is intended for processing all or selected columns in a similar fashion one by one. It’s a bit tricky to setup (probably the most complex action in EasyMorph), but is very versatile. See an example here: How to use iterate column action
It is recommended to upgrade to v4.3 or later. It this version, the “Input” action (required when “Iterate columns” is used) has a button for automatically populating the input dataset, which is not so trivial in earlier versions.
Pros: possible to process very large tables, any calculation logic can be applied
Cons: non-trivial to learn and configure
Thanks für Your answer, Dmitry.
I tried the first approach now and it works so far for this example. Now I got the same problem with the Deduplicate-function. I want to deduplicate records for all key fields of the target table. I guess, I will have a look at the second approach.