Updating SQL Case Statement

[mmvcans]

The object of the exercise is to run a distinct query from a category, find the newly added codes, push an email to determine tax status for the new codes, then add the new codes to specific bucketing with the case statement in the query. 

The query is something like

case when COL1 in ('x', 'y') then 'A' when COL2 in ('u','v') then 'B' else 'C' end as label

 with the objective updating the ('x','y') and ('u','v') parameters to ('x','y','z') and ('u','v','w') of z and w are newly identified codes from the DB.

I've tried to use the dynamic input tool with 'Replace a Specific String' and 'SQL: Update WHERE Clause' with the input data source template as the query in .txt format, but neither output the updated query.

Does anyone have another solution?

[DavidP · Accepted solution]

Hi @mmvcans 

I would probably do it by setting up the connection and base SQL statement with an Input Data tool and then turn it into a batch macro where you can update the values by calling the macro from another workflow.

Something like this

[mmvcans]

Thanks @DavidP - worked like a charm!