Skip to main content
  • EN
  • Home
  • Forums
  • FME Form
  • General
  • Using SchemaSetter assembles attribute in apparent random order - how do I get them in their original order?

I'm setting up a workbench that runs a SQL query on an Oracle db (using SQL Executor) and converts it into an ESRI Shapefile. The workbench is dynamic; we have about 200 pre-defined SQL queries stored in a config file that is retrieved at runtime based on user input and stored in a parameter. I've got everything in the workbench set up and working fine; a user can pick which query they want to use, the SQL Executor will execute the appropriate query, Schema Setter will build a list of attributes based on the SQL Executor, and they will be written to the Shapefile.

 

The problem is the order in which the attributes appear seems to be completely random. If my SQL query is "select a, b, c, d, e, f ... from table" my resulting shapefile will show the attributes in something like e, d, f, a, c, b as opposed to the order in which they were queried. The order is the same each time so I guess it's not TOTALLY random, but I have no idea how it's getting the order it's getting.

 

Unfortunately, each SQL query we're using queries a different table and thus has different column names so this isn't something I can sort out in the writer. Alphabetical order is also not what I'm looking for. Is there any way to force SchemaSetter to return the list in the order that was set up by the SQL Query?

I think it might be that the schema is being returned in the order that the fields are in the database. The SELECT is just specifying which fields are being queried, not their order.

 

The Schema is just a list. So you could explode it out, reorder the features, then recreate the schema and merge it to the appropriate features

 

I think it might be that the schema is being returned in the order that the fields are in the database. The SELECT is just specifying which fields are being queried, not their order.

 

The Schema is just a list. So you could explode it out, reorder the features, then recreate the schema and merge it to the appropriate features

 

It doesn't seem to be in that order; the ID field is first in the list of columns in the database and the output has it buried somewhere in the middle. Very strange!

 

As for ListExploder, is there a way to do this dynamically? Since I'm working with multiple queries, one query might have columns A, B, C, D, E, F and another query might return A, B, E, F, G, H. I was playing around with ListExploder but when I connect that to the SchemaSetter it requires me to put in something for the List Attribute field, but when I click the dropdown it says "No List Attributes Available".

It doesn't seem to be in that order; the ID field is first in the list of columns in the database and the output has it buried somewhere in the middle. Very strange!

 

As for ListExploder, is there a way to do this dynamically? Since I'm working with multiple queries, one query might have columns A, B, C, D, E, F and another query might return A, B, E, F, G, H. I was playing around with ListExploder but when I connect that to the SchemaSetter it requires me to put in something for the List Attribute field, but when I click the dropdown it says "No List Attributes Available".

You need to expose the list and then it will appear in the ListExploder

Screenshot 2020-12-16 091604

It doesn't seem to be in that order; the ID field is first in the list of columns in the database and the output has it buried somewhere in the middle. Very strange!

 

As for ListExploder, is there a way to do this dynamically? Since I'm working with multiple queries, one query might have columns A, B, C, D, E, F and another query might return A, B, E, F, G, H. I was playing around with ListExploder but when I connect that to the SchemaSetter it requires me to put in something for the List Attribute field, but when I click the dropdown it says "No List Attributes Available".

Thanks, I feel like this is making some progress. I've gotten this far, but frankly, I don't know where to go next (sorry - fairly new to FME still!). Once I've exploded the list, what transformer do I use to re-order the fields?

It doesn't seem to be in that order; the ID field is first in the list of columns in the database and the output has it buried somewhere in the middle. Very strange!

 

As for ListExploder, is there a way to do this dynamically? Since I'm working with multiple queries, one query might have columns A, B, C, D, E, F and another query might return A, B, E, F, G, H. I was playing around with ListExploder but when I connect that to the SchemaSetter it requires me to put in something for the List Attribute field, but when I click the dropdown it says "No List Attributes Available".

for hte most basic of sorts, (ie alphabetical) you can use the Sorter (or ListSorter - don't need to explode the list).

If the order is not numeric/alphabetical then approaches I've used previously is to store the required order in a seperate file (csv, excel) then read that in separately (one row per field). Then pass it through a counter, merge it with the appropriate field from your schema, sort on the count, then recreate the schema list

Reply


Community Stats

30,161
Posts
114,493
Replies
37,813
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.