Skip to main content
Solved

Using SQL to create a parameter value

  • September 26, 2017
  • 8 replies
  • 240 views

jackyd
Contributor
Forum|alt.badge.img+10

Hi,

I wish to calculate some parameters values based on tables in my database, i need the max value of the pid field. These value would then be passed to the counter transformer to start the counter off from this max value.

Currently i do this by using a sql executor to calculate the values then a feature merger to add these values to the records, this how ever is very memory intensive and causing the workbench to crash.

Is is possible to calculate the sql and store the value in a parameter?

Best answer by takashi

Yes, depending on the database format, a scripted parameter could be a solution.

However, I don't think the SQLCreator + FeatureMerger approach would be very memory intensive if you can set "Yes" to the Supplier First parameter in the FeatureMerger. The SQLCreator runs before any reader, so you can set "Yes" to the parameter if the Requestor features are read from a reader.

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

8 replies

Forum|alt.badge.img+2
  • September 26, 2017

I've used a Python Scripted Parameter in the past connecting to Oracle using cx_oracle module and that worked well. Another approach would be a WorkspaceRunner where the first calculates the value before passing into the second as a parameter.


takashi
Influencer
  • Best Answer
  • September 26, 2017

Yes, depending on the database format, a scripted parameter could be a solution.

However, I don't think the SQLCreator + FeatureMerger approach would be very memory intensive if you can set "Yes" to the Supplier First parameter in the FeatureMerger. The SQLCreator runs before any reader, so you can set "Yes" to the parameter if the Requestor features are read from a reader.


jneujens
Forum|alt.badge.img
  • September 26, 2017

One way to do this, is by creating a scripted python parameter, and perform this query within python?

You can avoid using a FeatureMerger by executing a query that already adds this maximal value as field. The only precondition (as for as I know), is that you will need to group your values with another attribute. 

 

 

For example this query will give the maximal value, but grouped for ID. I am not an SQL expert, so somebody might know how to do this without grouping?
select ID, VALUE, 
  (select MAX(VALUEfrom mytable where ID = t.ID) as MaxValue
from mytable t

Forum|alt.badge.img
  • September 26, 2017

You can also use the Sampler, VariableSetter/VariableRetriever option instead of the FeatureMerger.

  • Counter to set a value on your feature starting at 1
  • Sampler to use the first feature to query the database for max value and VariableSetter to save the value
  • VariableRetriever + ExpressionEvaluator to set pid at max value + counter


takashi
Influencer
  • September 26, 2017
takashi wrote:

Yes, depending on the database format, a scripted parameter could be a solution.

However, I don't think the SQLCreator + FeatureMerger approach would be very memory intensive if you can set "Yes" to the Supplier First parameter in the FeatureMerger. The SQLCreator runs before any reader, so you can set "Yes" to the parameter if the Requestor features are read from a reader.

Alternatively this workflow could also be possible, since multiple global Counters having the same name work as an identical counter at runtime. The second Counter starts with (max_pid + 1). Note that the SQLCreater should run at first.

 


austinh
Contributor
Forum|alt.badge.img+4
  • Contributor
  • September 26, 2017

To get the highest value for pid (assuming it is an int), you can query your database table:

SELECT TOP 1 pid

FROM tableName

ORDER BY pid DESC


jackyd
Contributor
Forum|alt.badge.img+10
  • Author
  • Contributor
  • September 26, 2017

Thank you everyone, some good ideas here, @takashi as usual you have clear the fog with a simple solution


steveatsafe
Safer
Forum|alt.badge.img+12
  • Safer
  • September 26, 2017

If you want to keep it all in FME I recommend using a SQLCreator (may need to set the Run Order) where you return the max value from the table in question.  Depending on the database, the syntax is pretty similar.


select max(pid)+1 as M_PID from tableA  

for Oracle (ensure you use UPPER case in exposing M_PID as it seems Oracle returns this into FME as uppercase)

 OR 


select max(ID)+1 as m_pid from [tableA] 

 for SQL Server.  

Expose m_pid (or M_PID in Oracle) in your SQLCreator then pass this to the VariableSetter.  In the other flow, pull in the the max value using the VariableRetriever and apply it to the Counter.  

There's lots of great ideas above from the other users.  

0684Q00000ArKhpQAF.png


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