To ensure our database's data integrity, which is periodically enriched with measurement data governed by users, we use FME to create data validation reports in Excel. With these reports, users can correct their data iteratively.
To make this work, we use SQL statements stored as attributes in a table. Depending on the report to be generated, SQL statements are fed into a SQLExecutor transformer as attributes. At the time of writing, we employ close to 500 validation queries, each with their own set of columns as well as amount of columns. The picture below shows how the queries are inserted into the SQLExecutor as an attribute.
This is then routed to a dynamic Excel writer by using the SchemaSetter custom transformer. The Excel report is generated as expected, except for the column order, which is sorted alphabetically instead of the order specified by the (dynamic) SQL queries.
Is there a way to preserve the column order specified by the SQL queries instead of having them sorted alphabetically?
Our current workaround is to manually add a prefix to the attributes in the SQL statements themselves, but this is not a desired solution.
The full workspace looks like this:
I think your only other option is to somehow use the sql queries to manually create the schemas with the columns in the order you want. Not sure how complex your sql queries are as to whether that is remotely feasible.
