I have a flow based around a main SQLExecutor that runs of different levels of aggregation depending on the input parameters, and the most conveinient and efficient way of doing that is to add or remove parts from my SQL query based on said parameters. While the problem looks simple on its face, I have not been able to find a good solution for that.
As evidenced by the following thread, FME doesn’t have a function for this kind of conditional string substitution. The best it has is the ternary operator.
However, said ternary operator is only supported in the Math Editor, not the Text Editor that SQLExecutor uses. The next best thing I have found is to use the @Evaluate function to give me access to Tcl’s ternary operator, provided I can express these sections of my query in a way that is valid in TCL.
select
@Evaluate("$(WHOLE_DAY)==YES ? {date_trunc('day', period_start_time, 'America/Toronto') as period_start_day,} : {}")
@Evaluate("$(COMPUTE_HOURLY)==YES ? {date_trunc('hour', period_start_time, 'America/Toronto') as period_start_hour,} : {}")
-- ...
EXTRACT (EPOCH FROM SUM("duration")) as duration_sec
from "my_table"
where
TIMESTAMP WITH TIME ZONE '@DateTimeFormat(@Value(zoned_start_bound),ISO)' <= "period_start_time"
AND "period_start_time" < TIMESTAMP WITH TIME ZONE '@DateTimeFormat(@Value(zoned_end_bound),ISO)'
GROUP BY GROUPING SETS
@Evaluate("$(WHOLE_DAY)==YES ? {(date_trunc('day', period_start_time, 'America/Toronto')),} : {}")
@Evaluate("$(COMPUTE_HOURLY)==YES ? {(date_trunc('hour', period_start_time, 'America/Toronto')),} : {}")
()I seem to be getting issues with the slash getting substituted by *1.0/ in my strings, though, despite all the bracket-quoting, because the query comes out like this:
select
date_trunc('day', period_start_time, 'America*1.0/Toronto') as period_start_day,
-- ...
EXTRACT (EPOCH FROM SUM("duration")) as duration_sec
from "my_table"
where
TIMESTAMP WITH TIME ZONE '2024-05-15T00:00:00-04:00' <= "period_start_time"
AND "period_start_time" < TIMESTAMP WITH TIME ZONE '2024-05-22T00:00:00-04:00'
GROUP BY GROUPING SETS
(date_trunc('day', period_start_time, 'America*1.0/Toronto'), lnbts_name,lncel_name),
()
This happens in the ExpressionEvaluatior as well, so it’s not an issue with SQLExecutor specifically. I would submit a bug report, but I can’t seem to be able to log to the support website at this time.
Are there any other ways of conditionally enabling various sections of a query like that?
