: Dynamic SQL Query with Keywords from Lookup Table in Workato
Current SQL Query:
SELECT * FROM ( SELECT *, TRIM( COALESCE( CASE WHEN LOWER(TRIM(formula_1)) LIKE '%payments%' THEN 'Payments' ELSE '' END || CASE WHEN LOWER(TRIM(formula_1)) LIKE '%services%' THEN '|Services' ELSE '' END || CASE WHEN LOWER(TRIM(formula_1)) LIKE '%vendor%' THEN '|Vendor' ELSE '' END, ' ' ) ) AS matched_keywords FROM netsuite_records ) AS subquery WHERE matched_keywords <> '';
In this query, the keywords (payments, services, etc.) and columns (formula_1, formula_2, etc.) are hardcoded. I want to replace this logic with a dynamic solution where:
- Fetches Keywords and Columns Dynamically: Keywords and corresponding columns (e.g., payments -> formula_1, services -> formula_1) come from a lookup table.
- Dynamic Query Construction: The query dynamically matches keywords from the lookup table with specified columns from the netsuite_records dataset.
For example:
Lookup Table Data:
Keyword Columns
| payments | formula_1 |
| services | formula_1 |
| vendor | formula_1 |
Expected Query Behavior:
The query should dynamically construct CASE conditions based on the lookup table data, such as:
CASE WHEN LOWER(TRIM(formula_1)) LIKE '%payments%' THEN '|Payments' ELSE '' END || CASE WHEN LOWER(TRIM(formula_1)) LIKE '%services%' THEN '|Services' ELSE '' END || CASE WHEN LOWER(TRIM(formula_1)) LIKE '%vendor%' THEN '|Vendor' ELSE '' END
Key Requirements:
- Use the Lookup Table action in Workato to fetch the entire lookup table.
- Dynamically construct the SQL query to match keywords with the specified columns.
Approach Options:
- Use the lookup table data to create a collection in Workato and query the collection.
- Avoid creating a collection and directly pass the lookup table data as input to the SQL query.
- use python snippet to get data from the lookup_table and check the conditions with netsuit_records
What would be the best way to achieve this dynamic query construction in Workato? Any suggestions or best practices would be greatly appreciated.
