Skip to main content
Solved

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

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.

View original
Did this help you find an answer to your question?

4 replies

redgeographics
Celebrity
Forum|alt.badge.img+49

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.

garbadek
Contributor
Forum|alt.badge.img
  • garbadekContributor
  • Author
  • Contributor
  • 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+49
  • redgeographicsCelebrity
  • Celebrity
  • 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.

garbadek
Contributor
Forum|alt.badge.img
  • garbadekContributor
  • Author
  • Contributor
  • August 3, 2023
redgeographics wrote:

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


Helpful Members This Week

Community Stats

31,316
Posts
118,754
Replies
38,904
Members

Latest FME

Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie 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.