Convert SQL WHERE clause to Tester test clauses

Could the InlineQuerier help you with this issue?

https://docs.safe.com/fme/html/FME-Form-Documentation/FME-Transformers/Transformers/inlinequerier.htm

Maybe it is easier than transforming the queries into tester clauses.

Have you looked at the SQLExecutor? You can use Case expressions to create extra variables from the where clauses from your Excel file.

Make sure the where clauses are written as FME expects them (like enclosing field names in double quotes).

In FME add the where clauses to a list, so only one feature triggers the SQLExecutor.

Then add case expressions to the SQL Statement, like

case when @Value(WhereClauses{0}.WhereClause) then 'True' end as TEST0

In my tests this did not work in combination with select *, so you will have to explicitly name the fields to read. Also explicitly add the geometry field if you have one, and expose the attributes.

Afterwards you can simply test on the extra attributes (like TEST1 = 'True').

Thanks for the reply. I think the InlineQuerier might indeed be the solution, but I can't seem to figure out how to extract the clause-value from the Excel file and implement it dynamically in the InlineQuerier.

case when @Value(WhereClauses{0}.WhereClause) then 'True' end as TEST0

If I understand the documentation correctly the SQLExecutor executes queries against a database, but I am trying to execute them against features that are already in my workspace. Therefore I don't think the SQLExecutor is of help for me. Good looking out with the part about double quotes, I would have missed that for sure.

case when @Value(WhereClauses{0}.WhereClause) then 'True' end as TEST0

You are right the SQLExecutor reads features from a database. You would have to modify your workspace to accomodate the SQLExecuter.

Being honest, I have never used that transformer. But, if you open de SQL Query parameter, it should offer you the excel attributes to build the query:

There is a section called "Database Tables" that should contain the attributes of the incoming features.

As far as I have seen, it is not possible to use dynamic SQL in the InlineQuerier.

Whoa this is nice, thank you! Wasn't expecting another update on this thread. I really appreciate you going the extra length.

I guess your username checks out! Have a great weekend.

Just to add to the best answer by Geomancer - you can also wrap the InlineQuerier inside a custom transformer and supply user parameters that way instead of having to set off a workspace runner.

Scrap that - wrapping it in a custom transformer doesn’t work when passing in an attribute unfortunately.

The workaround I’ve used is to use a PythonCaller to roughly convert SQL to a python expression. This will only work for simple expressions but it’s good enough for my use-case.

import fme
import fmeobjects

class FeatureProcessor(object):
    def __init__(self):
        pass

    def input(self, feature):
        # Extract the SQL WHERE clause from the user parameter
        where_clause = feature.getAttribute('SQL Where Clause') #Add your WHERE Clause attribute here

        # Build a function to evaluate the WHERE clause
        def evaluate_where_clause(feature, clause):
            # Use regex to split the clause into tokens while keeping parentheses and quotes
            tokens = re.findall(r'(\b\w+\b|[><=!]+|["\'][^"\']*["\']|[()])', clause)
            #tokens = clause.split() # need spaces between operators and att names/values
            expression = ""
            i = 0
            while i < len(tokens):
                token = tokens[i]

                #Convert the SQL operators to python syntax
                if token.upper() in ['AND', 'OR']:
                    expression += f" {token.lower()} "
                elif token == '=':
                    expression += " == "
                elif token == '!=':
                    expression += " != "
                elif token in ['<', '>', '<=', '>=']:
                    expression += f" {token} "

                # Check if the token is an attribute or a value                    
                else:
                    #This is a value and is wrapped in a single '
                    if token.startswith("'") and token.endswith("'"):
                        expression += token

                    #This is an attribute and is wrapped in a double " 
                    elif token.startswith('"') and token.endswith('"'):
                        attribute_name = token.strip('"')
                        attribute_value = feature.getAttribute(attribute_name)

                        if isinstance(attribute_value, str):
                            expression += f"'{attribute_value}'" # string expression e.g. "Att" = 'Apples' (requires quotes)
                        else:
                            expression += f"{attribute_value}" # numerical expression e.g. "Att" >= 2 (no quotes)
                i += 1
            # Evaluate the constructed expression
            #print(expression)
            return eval(expression)

        # Evaluate the feature against the WHERE clause
        if evaluate_where_clause(feature, where_clause):
            # If the feature passes the WHERE clause, output it
            self.pyoutput(feature)

    def close(self):
        pass