Skip to main content
  • EN

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?

@vlroyrenn ,

If you haven’t been able to log in to the support website please let us know and we can followup with our internal team to get this sorted.  Ensure you are using the ‘correct’ email to log in as.  Some customers may have used their personal email while others would have used a corporate email when accessing the support portal.

For this one, we will need to take a deeper look at it I suspect and a new support ticket would be good - once you can get logged in.

Cheers,

When doing this with SQL where clauses to select data, I have use an AttributeCreator with conditional statements that will either be a valid part of a statement “AND column = ‘value’ “, or will be an empty string. Then these additional components of the where clause get all concatenated together to create whatever SQL query is correct for that specific feature.

For your group by, create an attribute of ‘GroupBy_Day’, make it conditional,

if $(WHOLE_DAY) = YES - then set value to (date_trunc('day', period_start_time, 'America/Toronto')

else blank or empty string

Then create a second attribute of ‘GroupBy_Hour’, make it conditional,

if $(COMPUTE_HOURLY) = YES - then set value to (date_trunc('hour', period_start_time, 'America/Toronto'))

else blank or empty string

 

Then the end of your SQL statement reads:

…

GROUP BY GROUPING SETS

     @Value(GroupBy_Day) @Value(GroupBy_Hour)

 

@vlroyrenn ,

If you haven’t been able to log in to the support website please let us know and we can followup with our internal team to get this sorted.  Ensure you are using the ‘correct’ email to log in as.  Some customers may have used their personal email while others would have used a corporate email when accessing the support portal.

For this one, we will need to take a deeper look at it I suspect and a new support ticket would be good - once you can get logged in.

Cheers,

That turns out to have been an issue with Firefox’s strict tracking protection mode, which has now been reported to them. I’ll be sure to file a separate issue with support regarding that slash issue.

When doing this with SQL where clauses to select data, I have use an AttributeCreator with conditional statements that will either be a valid part of a statement “AND column = ‘value’ “, or will be an empty string. Then these additional components of the where clause get all concatenated together to create whatever SQL query is correct for that specific feature.

For your group by, create an attribute of ‘GroupBy_Day’, make it conditional,

if $(WHOLE_DAY) = YES - then set value to (date_trunc('day', period_start_time, 'America/Toronto')

else blank or empty string

Then create a second attribute of ‘GroupBy_Hour’, make it conditional,

if $(COMPUTE_HOURLY) = YES - then set value to (date_trunc('hour', period_start_time, 'America/Toronto'))

else blank or empty string

 

Then the end of your SQL statement reads:

…

GROUP BY GROUPING SETS

     @Value(GroupBy_Day) @Value(GroupBy_Hour)

 

I was hoping to be able to that fully within the SQL string editor, but that seems to work pretty well. It also lets me use a ListConcatenator to add the correct amount of commas as needed between each grouping set/AND between each condition.

Alternatively, I realized you could flip the conditions from my OP and just insert a line comment marker at the beginning of each line that’s not needed. It was causing issues with separators, though, so I’ll probably just go with your solution.

Reply


Community Stats

30,108
Posts
114,290
Replies
37,743
Members
Terms & ConditionsCookie 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.