Articles in this section

Build a database query based on multiple exported values

Adel Haider
Updated

You can retrieve information from a database with a single SQL query that combines values from multiple exported records. Running one query instead of many improves performance, since the database is a shared resource. To do this, use a JavaScript function to collect the exported values into lists, then reference those lists in the query.

This example builds two lists of values from an exported file:

  • A list of concession numbers
  • A list of branch numbers

A script separates the exported values into the two lists. You then query a database to return the records that match the values in those lists.

1. Write the JavaScript function

Create a function that loops through the data (the payload) and builds two lists, listOfConcessionNumbers and listOfBranchNumbers. The function places the lists back into the payload so you can reference them later when querying the database.

function blockQuery(options) {
 var listOfConcessionNumbers = "";
 var listOfBranchNumbers = "";

 options.data[0].FileDetailRecord.forEach(function (item) {
 if (listOfConcessionNumbers.length != 0) { listOfConcessionNumbers += ","; }
 listOfConcessionNumbers = listOfConcessionNumbers + "'" + item.ConcessionNumber + "'";

 if (listOfBranchNumbers.length != 0) { listOfBranchNumbers += ","; }
 listOfBranchNumbers = listOfBranchNumbers + "'" + item.BranchNumber + "'";
 });

 options.data[0].listOfConcessionNumbers = listOfConcessionNumbers;
 options.data[0].listOfBranchNumbers = listOfBranchNumbers;

 return { data: options.data, errors: options.errors, abort: false };
}

2. Build the query in the lookup step

Create the SQL query for the lookup step. At this point, the two lists are available as comma-separated string variables that you can reference in the query.

SELECT LOCATION_ID
 FROM LOCATIONS
 WHERE CONCESSION_NUMBER in ({{{data.listOfConcessionNumbers}}})
 AND RIGHT (LOCATION_ID,4) in ({{{data.listOfBranchNumbers}}})
 AND LEFT (LOCATION_ID,2) = STORE_PREFIX;

Note the Handlebars syntax used to reference the lists built in the JavaScript function. The triple curly braces ({{{ }}}) insert each list as raw text, so integrator.io doesn't escape or quote the comma-separated values before they're placed into the query.

3. Trigger the function before the database lookup

The lists must exist in the payload before the lookup runs, so call the function at a step where all the data you need is already available — in this example, the FTP transfer step that precedes the lookup. Use a hook on that step to call blockQuery() and process the records before they pass downstream.

After the query runs, apply the appropriate response mapping so the lookup data is available to later steps.

Notes and considerations

  • The database should return a record for each item in the list, plus an empty record when a match isn't found.
  • Check the maximum number of items your database allows in an IN clause, and stay within that limit.