Skip to main content
  • EN

I have a database table that contains many attributes, one of which is controlled by a domain of acceptable values.

imageI have a list containing a unique list of these value

imageI want to step through this list and each time through the loop select just the records from the table that would be selected if the unique value were inserted into in query:

SELECT * FROM Table WHERE AttribInQuestion = WhateverColor

For instance, "Red" the first time through the loop would return:

imageI've setup my Reader to read the SQL table I want to query, generated a List of my unique values, and created a TestFilter. The TestFilter works if I just type 'Red' into it, but I cannot figure out how to feed the filter a variable to save my life. I'm not asking about the looping (I've created a custom transformer for that), what I'm asking about is how to insert the 'Red' value into my filter using a variable?

 

Thank you for any help you can provide.

 

K

Instead of using a reader to read the database, try using a FeatureReader

 

So read your list of unique values with a regular reader, that should give you 4 features. Route them to a FeatureReader and then you can use the attribute in the where clause.

Thank you for a quick response, but I'm sorry I'm not sure I understand. I think I might be able to help myself by restating my question using a small/simple example of my actual data:

I have this table in an SQL Enterprise geodatabase (not a feature class, just a table registered with the GDB)

imageIn this example I'm using an "esri geodatabase sde..." reader and I'm using the DuplicateFilter transformer to filter for the unique ParentTable name:

imageSo far exactly what I want (I.E. the value "Parcels" is the only ParentTable value in my table so it constitutes all unique values in this case, but having one value will not be the norm, but looping can come after I figure this part out).

I have another table (not a feature class) registered with the same enterprise GDB that looks like this:

imageI want to use the value of the ParentTable attribute coming out of the Unique port in my DuplicateFilter (I.E. the value "Parcels") to query my SchemaKeyProduction table such that I only get the three records that would be returned if I typed this into SQL:

 

Select * From SchemaKeyProduction Where SourceFile = 'Parcels'

 

I've tried creating a list and indexing the list and exploding the list. I've tried attribute manager. I've tried setting and getting an attribute, and all combination of these things, but no matter what I try I cannot get it to return my matching records. All say "Unmatched". If I just enter "Parcels" into the TestFilter, the three records pop out no problem.

 

I'm pretty new to FME so I'm sorry if I'm missing something simple. An example would be great though. Again, I appreciate your help.

I've simulated this with a PostGIS database:image.pngThe output of my AttributeCreator is similar to your ListExploder, one or more features with an attribute value you want to use to select from the database.

I'm using a FeatureReader transformer rather than a reader because it has the advantage that it can use attributes of the initiator feature. So here I'm using a WHERE clause that's filled by the attribute type coming in from my initiator.

 

In essence this will perform one query per feature, SELECT * FROM table] WHERE column] = value]

 

Hope this helps.

I've simulated this with a PostGIS database:image.pngThe output of my AttributeCreator is similar to your ListExploder, one or more features with an attribute value you want to use to select from the database.

I'm using a FeatureReader transformer rather than a reader because it has the advantage that it can use attributes of the initiator feature. So here I'm using a WHERE clause that's filled by the attribute type coming in from my initiator.

 

In essence this will perform one query per feature, SELECT * FROM table] WHERE column] = value]

 

Hope this helps.

It took some forging and hammering on the formatting (experimenting with quotes and a lack thereof, etc.) but that worked. One part of the puzzle down. Thank you for your help and patience.

 

K

Reply


Community Stats

30,162
Posts
114,483
Replies
37,810
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.