The 4D command ARRAY TO SELECTION provides a convenient way to build a selection of records from a collection of data stored in an array.

The SQL implementation in 4D provides an elegant analog to this using subqueries. However subqueries only work within the same database. What if you want to use data from one database to query another database? There are a few different ways to accomplish this via SQL. Note that in all cases it is assumed the developer already has the query constraint values in an array. Note also that neither the select item list nor destination are specified as these are not important for understanding these techniques.

Execute a query for each constraint and accumulate the results.

For ($i;1;Size of array($constraintsArray_al)    Begin SQL    SELECT ...    FROM TheTable    WHERE TheField = :$constraintsArray_al{$i}    INTO ...    End SQL // Don't forget to accumulate the results after each statement... End for

This is, of course, not terribly elegant nor efficient as it results in one request to the target database per constraint value.

Execute a single Query with multiple WHERE constraints joined via the OR operator.

$statement_t:="SELECT ... FROM TheTable WHERE " For ($i;1;Size of array($constraintsArray_al)     // Don't add "OR" to the first constraint.    If ($i#1)       $statement_t:=$statement_t+" OR "    End if       $statement_t:=$statement_t+" TheField = "+String($constraintsArray_al{$i}) End for $statement_t:=$statement_t+" INTO ..." Begin SQL    EXECUTE IMMEDIATE :$statement_t; End SQL

This works fine but unnecessarily bloats the SQL statement with the redundant "TheField =" and "OR"'s.

Note that, since EXECUTE IMMEDIATE is used, this example only works when connecting to 4D databases (i.e. not ODBC data sources).

Execute a single query and specify the list of constraint values using the IN predicate

$statement_t:="SELECT ... FROM TheTable WHERE TheField IN ( " For ($i;1;Size of array($constraintsArray_al)     // Don't add "," to the first constraint.    If ($i#1)       $statement_t:=$statement_t+","    End if       $statement_t:=$statement_t+String($constraintsArray_al{$i}) End for $statement_t:=$statement_t+" ) INTO ..." Begin SQL    EXECUTE IMMEDIATE :$statement_t; End SQL

Here is an example of the statement (formatted for easier readability):

SELECT ... FROM TheTable WHERE TheField IN ( 25714, 15748, 10467, 28192 ) INTO ...

Since the query constraints all apply to the same field, the IN predicate provides a convenient way to list all the values in a single statement.

Note that, since EXECUTE IMMEDIATE is used, this example only works when connecting to 4D databases (i.e. not ODBC data sources).