I am attempting to build an ExecuteSQL with SQLExplorer that will return a list of ID's based on querying a return delimited variable. So far, the best way that's been determined to do this is by using the 'IN' parameter in place of the '=' parameter. However, I've so far been unable to get the query to function correctly.

Here is the query with the where the '=' has been replaced by 'IN':

Code: Select all

Let ( [

// Define Carriage Return Substitution Character
ReturnSub = "\n" ;

// Enable the second line here if you want the header in your results

header = "";
//header = "b._id_table3";


// Define Table variables
aTABLE2 = Quote ( GetValue ( Substitute ( GetFieldName ( TABLE2::_id_table2 ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;
bTABLE3 = Quote ( GetValue ( Substitute ( GetFieldName ( TABLE3::_id_table3 ) ; "::" ; ¶ ) ; 1 ) ) & " b" ;

// Define Field Variables
a_id_table2 = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( TABLE2::_id_table2 ) ; "::" ; ¶ ) ; 2 ) ) ;
b_id_table3 = "b." & Quote ( GetValue ( Substitute ( GetFieldName ( TABLE3::_id_table3 ) ; "::" ; ¶ ) ; 2 ) ) ;
bid_table2 = "b." & Quote ( GetValue ( Substitute ( GetFieldName ( TABLE3::id_table2 ) ; "::" ; ¶ ) ; 2 ) ) ;

// Build SQL Query
q =
"SELECT DISTINCT " & b_id_table3 & "
FROM " & aTABLE2 & "
INNER JOIN " & bTABLE3 & " ON " & a_id_table2 & " = " & bid_table2 & "
WHERE " & a_id_table2 & " IN (?) " ;

// Run SQL Query
result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; $myVariableHere ) ] ; 

// Clean up carriage returns
 List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  ) )  )

The problem is that the IN parameter seems to want me to specify the number of items in my list within the actual query. So for example, if I have a list with 3 items, the line with 'IN' would look like

Code: Select all

WHERE " & a_id_table2 & " IN (?, ?, ?) " ;

If I have a list of items with 10 items, it functions correctly if I do this

Code: Select all

WHERE " & a_id_table2 & " IN (?,?,?,?,?,?,?,?,?) " ;

How would I go about correctly formatting this query?

Hi,

Thanks for the post!

Right, Parameterizing IN clauses using the ExecuteSQL function is tricky. I often finding myself just substituting the In values right into the query as you can use a variable. Something like:

q =
"SELECT DISTINCT " & b_id_table3 & "
FROM " & aTABLE2 & "
INNER JOIN " & bTABLE3 & " ON " & a_id_table2 & " = " & bid_table2 & "
WHERE " & a_id_table2 & " IN (" & $myvariablehere & ") "

You need to format your variable to include the single quotes, if they're text values, and be comma separated, but that's doable. Using parameters is considered best practice in SQL, but here's a case where I (personally) make an exception for FMSQL.

In FMSQL you can have more parameters then you need, but not less. Any extras will be ignored, so you can also do something like this:

"ExecuteSQL ( $sc_Query ; $sc_ColumnDelimiter ; $sc_RowDelimiter ;

$sc_Argument [ 1 ] ;
$sc_Argument [ 2 ] ;
$sc_Argument [ 3 ] ;
$sc_Argument [ 4 ] ;
$sc_Argument [ 5 ] ;
etc."

and then populate your variables as needed, but if you're potentially going to have 1000 parameters, then you'd need up to $sc_Argument [ 1000 ], which is why I prefer the "Variable In Query" method.

Finally, if possible, you can also try a sub-query in your IN() rather than passing literal values of your return delimited variable. We have an example of using sub-queries on our blog here:

http://www.seedcode.com/sql-subqueries-in-filemaker/

That way you don't have to worry about a dynamic number of "?s"

I hope that helps and please let me know if you have any other questions.

Cheers!