compare collection with lookup table

nusair — Tue, 31 Dec 2024 11:34:05 GMT

: 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.

topic compare collection with lookup table in Workato Pros Discussion Board

compare collection with lookup table