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?
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.
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.
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(VALUE) from mytable where ID = t.ID) as MaxValue
from mytable tYou can also use the Sampler, VariableSetter/VariableRetriever option instead of the FeatureMerger.
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.
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
Thank you everyone, some good ideas here, @takashi as usual you have clear the fog with a simple solution
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 atableA] 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.
