Hi,
I would use the SQLExecutor transformer.
Assuming that the table has a field called "recordID" for example, the transformer with this SQL statement creates a feature having an attribute named "numMatched" which stores the number of records matched with the condition specified as the "where" clause. i.e. the recordID matches with one of 1, 2, or 3.
-----
select count(*) as numMatched from your_table_name
where recordID in (1,2,3)
-----
Expose "numMatched" in the transformer parameter setting, you can then check whether the number is 0.
In the SQL Statement for the transformer, you can also use attribute values of the input Initiator feature as its components.
If you will apply the example above, you can create comma-separated ID list (e.g. 1,2,3) based on the CSV features using the Aggregator (or the ListBuilder) and the ListConcatenator, and then send the resulting feature to the SQLExcecutor as the Initiator. The SQL Statement setting will be:
-----
select count(*) as numMatched from your_table_name
where recordID in (@Value(comma_separated_id_list))
-----
Just be aware that you will have to quote each value with single quotations (e.g. '1','2','3'), if the data type of the "recordID" is not a numeric type. When you need to quote the values, consider using the StringConcatenator or the AttributeCreator before creating the comma-separated ID list.
Hope this helps.
Takashi
Thanks for the answer. I am having trouble with the SQLExcecuter, sorry I'm a noob
FME keeps telling me the sql statement is invalid and giving this error
"Must declare the scalar variable "@Value"
I am using the attributecreater to add single quotes then creating a list with listbuilder and then concatenated a string using the listconcatenator.
select count(*) as numMatched from dbo.tbPuddle_Project
where Local_Wetland_Name in (@Value(sql_concatenated))
I used an inspection point and determined sql_concatenated(encoded: utf-8) = 'Rob Puddle','frog pond','Indian Arm'
when I hardcode the list into the sql it works fine
The error has been detected by the SQL Server, there could be an issue on the syntax of "@Value()". It have to be interpreted to a character string (comma-separated names list) by FME before the querying.
Check:
- the function name "@Value" is strictly correct in case-sensitive?
- there isn't extra whitespace between "@Value" and the following"("?
Also try inserting "@Value(...)" to the statement by dragging or double-clicking the attribute name shown on the left pane of the editor.
I am insering the "@Value(....)" by double clicking. Also no whitespaces. I notice in the inspection point the sql_concatenated attribute has a encoded: utf-8 datatype but all my other attributes are text. could this be the problem? I tried adding
declare @list nvarchar(max)
set @list = convert(nvarchar(max), @Value(sql_concatenated))
I don't think the encoding is an issue. But I cannot specify the reason for the error anyway.
If you construct whole SQL statement as an attribute value and set it to the "SQL Statement" parameter, what happens?
This worked perectly! Thanks
One more stupid question when I create my list using listbuilder, I get a sublist for every attribute in my csv which is about 35 sublists! and no attributes. I have searched for a transformer that will take 1 attribute and create 1 list but haven't had any luck. The fme documentation says to get rid of unneeded lists as soon as possible not sure why they don't make it easy to avoid making more lists than needed.
Good to hear it worked fine.
To avoid creating unnecessary lists, you can use the AttributeRemover or the AttributeKeeper to remove unnecessary attributes (or to keep only necessary attributes) before creating the list.
Alternatively, you can also use the Aggregator to create efficiently a comma-separated attribute values list, instead of the ListBuilder+ListConcatenator.
Mode: Attributes Only
Keep Input Attributes: No
Attributes to Concatenate:
<select attribute(s) to concatenate>
Separator Character: ,
