Skip to main content
Solved

Set a single variable value and use it as input into a transformer

  • August 3, 2023
  • 4 replies
  • 290 views
garbadek
Contributor
Forum|alt.badge.img

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

Best answer by redgeographics

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.

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

4 replies

redgeographics
Celebrity
Forum|alt.badge.img+59
  • redgeographicsCelebrity
  • Celebrity
  • 3700 replies
  • August 3, 2023

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.

FME rocks! \m/
garbadek
Contributor
Forum|alt.badge.img
  • garbadekContributor
  • Author
  • Contributor
  • 8 replies
  • August 3, 2023

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.

redgeographics
Celebrity
Forum|alt.badge.img+59
  • redgeographicsCelebrity
  • Celebrity
  • 3700 replies
  • Best Answer
  • August 3, 2023

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.

FME rocks! \m/
garbadek
Contributor
Forum|alt.badge.img
  • garbadekContributor
  • Author
  • Contributor
  • 8 replies
  • August 3, 2023

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

Community Stats

32,070
Posts
121,771
Replies
39,696
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.