Skip to main content
Question

SQLCreator help, SQL code to integrate the Initiator fields into WHERE clause doesn't seem to work.

trevorm
Contributor
Forum|alt.badge.img+3

How different is SQLCreator from SQL SMS when writing an update query?.

Note: Not spatial data, just basic Feature to Feature compare to update and create my tblIriRut2020 table.

 

This is our SQL SMS Code from last year, the 2020 will be a .csv reader in FME

UPDATE [Preservation].[dbo].[tblIriRut2019]

  SET AADT = tblAnnualTrafficSCD.AADT_Year1      

FROM tblIriRut2019 tblIriRut2019

 LEFT JOIN tblAnnualTrafficSCD tblAnnualTrafficSCD ON tblIriRut2019.RoadName = tblAnnualTrafficSCD.ROADNAME AND

   tblIriRut2019.FromKM >= tblAnnualTrafficSCD.FromKM AND

   tblIriRut2019.FromKM < tblAnnualTrafficSCD.ToKM 

 

Replace tblIriRut2019 with my Initiator (currently only 3050100MUA feature)

SQLCreator Statement change needed to include Initiator

I'm not sure how to share my SQL connection data in a .fme so you can have a real life sample.

 

Another option:

If the SQLCreator cannot handle this, how do I make a where clause in a transformer like FileMerger so I can have a multiple WHERE clause with Functions like Less than or equal to?

File Merger Less than question

 

7 replies

david_r
Celebrity
  • david_rCelebrity
  • October 6, 2020

The SQLExecutor does little more than to do some parsing to evaluate functions or macros, before forwarding the statement(s) to the database engine. So whatever works in SSMS should also work the same in FME.

Based on the details above I'm not quite sure I understand the issue, but is it possible that you've forgotten to wrap feature attribute references into a @Value() function call?

For example:

UPDATE [Preservation].[dbo].[@Value(table_to_update)]
  SET AADT = tblAnnualTrafficSCD.AADT_Year1      
FROM @Value(table_to_update) ... etc...

Assuming that the there's a feature attribute table_to_update containing the name of the table to update.

Alternatively, if you're referening a published parameter, it would look like this:

UPDATE [Preservation].[dbo].[$(table_to_update)]
  SET AADT = tblAnnualTrafficSCD.AADT_Year1      
FROM $(table_to_update) ... etc...

Assuming that there's a published or private parameter called table_to_update.

trevorm
Contributor
Forum|alt.badge.img+3
  • trevormContributor
  • Author
  • Contributor
  • October 6, 2020
david_r wrote:

The SQLExecutor does little more than to do some parsing to evaluate functions or macros, before forwarding the statement(s) to the database engine. So whatever works in SSMS should also work the same in FME.

Based on the details above I'm not quite sure I understand the issue, but is it possible that you've forgotten to wrap feature attribute references into a @Value() function call?

For example:

UPDATE [Preservation].[dbo].[@Value(table_to_update)]
  SET AADT = tblAnnualTrafficSCD.AADT_Year1      
FROM @Value(table_to_update) ... etc...

Assuming that the there's a feature attribute table_to_update containing the name of the table to update.

Alternatively, if you're referening a published parameter, it would look like this:

UPDATE [Preservation].[dbo].[$(table_to_update)]
  SET AADT = tblAnnualTrafficSCD.AADT_Year1      
FROM $(table_to_update) ... etc...

Assuming that there's a published or private parameter called table_to_update.

Hello @david_r​ ,

 

Thanks so much for the quick reply, yes the (input_tablename) is my issue. 

 I want to replace the SQL table [tblIriRut2019] with my csv reader that is connected to the Initiator port and contains the 2020 data.

 

How do I write Initiator fields into the code? This is what my replacement started like:

UPDATE FMEFeature_Initiator (@Value(AADT))
SET AADT = tblAnnualTrafficSCD.AADT_Year1      
 
FROM FMEFeature_Initiator FMEFeature_Initiator
 
 LEFT JOIN tblAnnualTrafficSCD tblAnnualTrafficSCD ON FMEFeature_Initiator.@Value(RoadName) = tblAnnualTrafficSCD.ROADNAME AND 
 
  FMEFeature_Initiator.@Value(FromKM)   >=   tblAnnualTrafficSCD.FromKM AND 
 
  FMEFeature_Initiator.@Value(FromKM)   <   tblAnnualTrafficSCD.ToKM

End result is 1 field from my SQL table will be updated in my Result along with all of my Initiator data.

 

2020-10-06_12-04-48

trevorm
Contributor
Forum|alt.badge.img+3
  • trevormContributor
  • Author
  • Contributor
  • October 6, 2020
david_r wrote:

The SQLExecutor does little more than to do some parsing to evaluate functions or macros, before forwarding the statement(s) to the database engine. So whatever works in SSMS should also work the same in FME.

Based on the details above I'm not quite sure I understand the issue, but is it possible that you've forgotten to wrap feature attribute references into a @Value() function call?

For example:

UPDATE [Preservation].[dbo].[@Value(table_to_update)]
  SET AADT = tblAnnualTrafficSCD.AADT_Year1      
FROM @Value(table_to_update) ... etc...

Assuming that the there's a feature attribute table_to_update containing the name of the table to update.

Alternatively, if you're referening a published parameter, it would look like this:

UPDATE [Preservation].[dbo].[$(table_to_update)]
  SET AADT = tblAnnualTrafficSCD.AADT_Year1      
FROM $(table_to_update) ... etc...

Assuming that there's a published or private parameter called table_to_update.

@david_r​ I'm much better with .fme when I bring everything in as readers, and just use the transformers. I just don't know how to do a <= and > in FeatureMerger (last part of this post). 

 

As you can see This is Step 1 of 6 and about 5% of 1 annual process I have to complete by editing all that code manually each year. fme work space saves me hours and hours for each one of these. 

50meter Rollup page

 

david_r
Celebrity
  • david_rCelebrity
  • October 7, 2020
trevorm wrote:

Hello @david_r​ ,

 

Thanks so much for the quick reply, yes the (input_tablename) is my issue. 

 I want to replace the SQL table [tblIriRut2019] with my csv reader that is connected to the Initiator port and contains the 2020 data.

 

How do I write Initiator fields into the code? This is what my replacement started like:

UPDATE FMEFeature_Initiator (@Value(AADT))
SET AADT = tblAnnualTrafficSCD.AADT_Year1      
 
FROM FMEFeature_Initiator FMEFeature_Initiator
 
 LEFT JOIN tblAnnualTrafficSCD tblAnnualTrafficSCD ON FMEFeature_Initiator.@Value(RoadName) = tblAnnualTrafficSCD.ROADNAME AND 
 
  FMEFeature_Initiator.@Value(FromKM)   >=   tblAnnualTrafficSCD.FromKM AND 
 
  FMEFeature_Initiator.@Value(FromKM)   <   tblAnnualTrafficSCD.ToKM

End result is 1 field from my SQL table will be updated in my Result along with all of my Initiator data.

 

2020-10-06_12-04-48

What exactly is "FMEFeature_Initiator" in this context? Do you have a table in your database with that exact name? If not, then it's not going to work, you cannot mix FME features and SQL statements like that in the SQLExecutor.

If all your data is in a database table, use the SQLExecutor. If your data is a mix of FME features and database records, use something like the FeatureMerger, InlineQuerier, FeatureJoiner, DatabaseUpdater, etc.

david_r
Celebrity
  • david_rCelebrity
  • October 7, 2020
trevorm wrote:

@david_r​ I'm much better with .fme when I bring everything in as readers, and just use the transformers. I just don't know how to do a <= and > in FeatureMerger (last part of this post).

 

As you can see This is Step 1 of 6 and about 5% of 1 annual process I have to complete by editing all that code manually each year. fme work space saves me hours and hours for each one of these.

50meter Rollup page

 

A helpful trick to use when working with the SQLExecutor, is to first create an attribute containing the entire SQL statement using e.g. an AttributeCreator. You can then send the complete SQL statement that was generateed by FME to a Logger or Inspector, and copy/paste it into e.g. Toad. If the statement works as-is in Toad, then it should be OK in FME as well. But if Toad cannot execute it, then FME probably won't be able to either.

trevorm
Contributor
Forum|alt.badge.img+3
  • trevormContributor
  • Author
  • Contributor
  • October 9, 2020
trevorm wrote:

Hello @david_r​ ,

 

Thanks so much for the quick reply, yes the (input_tablename) is my issue. 

 I want to replace the SQL table [tblIriRut2019] with my csv reader that is connected to the Initiator port and contains the 2020 data.

 

How do I write Initiator fields into the code? This is what my replacement started like:

UPDATE FMEFeature_Initiator (@Value(AADT))
SET AADT = tblAnnualTrafficSCD.AADT_Year1      
 
FROM FMEFeature_Initiator FMEFeature_Initiator
 
 LEFT JOIN tblAnnualTrafficSCD tblAnnualTrafficSCD ON FMEFeature_Initiator.@Value(RoadName) = tblAnnualTrafficSCD.ROADNAME AND 
 
  FMEFeature_Initiator.@Value(FromKM)   >=   tblAnnualTrafficSCD.FromKM AND 
 
  FMEFeature_Initiator.@Value(FromKM)   <   tblAnnualTrafficSCD.ToKM

End result is 1 field from my SQL table will be updated in my Result along with all of my Initiator data.

 

2020-10-06_12-04-48

@david_r​ maybe you cannot see the images I'm including.. FMEFeature_Initiator = the attribute field you are inputting into the SQLExecutor. So in SQL SMS where I would write SQL code (not sure how to use toad) .. I would say Select * From database.dbo.tablename. how do I write tell SQLExecutor to look in the feature in the initiator feature? In the image attached I have green lines pointing to the text trying to show you .

have a great weekend!

david_r
Celebrity
  • david_rCelebrity
  • October 12, 2020
trevorm wrote:

Hello @david_r​ ,

 

Thanks so much for the quick reply, yes the (input_tablename) is my issue. 

 I want to replace the SQL table [tblIriRut2019] with my csv reader that is connected to the Initiator port and contains the 2020 data.

 

How do I write Initiator fields into the code? This is what my replacement started like:

UPDATE FMEFeature_Initiator (@Value(AADT))
SET AADT = tblAnnualTrafficSCD.AADT_Year1      
 
FROM FMEFeature_Initiator FMEFeature_Initiator
 
 LEFT JOIN tblAnnualTrafficSCD tblAnnualTrafficSCD ON FMEFeature_Initiator.@Value(RoadName) = tblAnnualTrafficSCD.ROADNAME AND 
 
  FMEFeature_Initiator.@Value(FromKM)   >=   tblAnnualTrafficSCD.FromKM AND 
 
  FMEFeature_Initiator.@Value(FromKM)   <   tblAnnualTrafficSCD.ToKM

End result is 1 field from my SQL table will be updated in my Result along with all of my Initiator data.

 

2020-10-06_12-04-48

You can only join tables that are already in the database, you cannot join e.g. a database table with the Initiator features as if it were a table.

The SQLExecutor will only  do string substitution (e.g. to insert attribute values in the SQL statement), it's not processing anything by itself.

Reply


Helpful Members This Week

Community Stats

31,300
Posts
118,696
Replies
38,878
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.