Using 'IN' to query a list

[James Gill]

I am attempting to build an ExecuteSQL using SeedCode's 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':

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

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

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

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

How would I go about correctly formatting this query?

[James Gill]

Well, I got it to function by hardcoding the variable into the query like so

WHERE " & a_id_table2 & " IN (" & $$MyVariableHere & ") " ;

But I really do not like doing this since now the calc doesn't even have a query parameter passed.  Why does manually mapping the variable work but I can't pass it as the actual search param as part of the ExecuteSQL function?

[beverly]

yes! IN requires a comma-delimited list. If numbers no need to quote (single-quote), if text, each value needs to be quoted ('a','b','c',....) The "?" parameter holder can format correctly if we specify each one (the quote will be added or not). It gets complicated when you have a "list" of parameters, as you've found.

See if this link helps. There may be other articles that can help you with this kind of query, as well.

http://filemakerhacks.com/2012/12/15/executesql-using-in-with-faux-dynamic-variables/

[timdietrich]

James --

 

I'm abbreviating the code, but here's one way to do it:

 

 

// Create a comma-separated list of IDs.

$myIDs = "1, 2, 3";

 

// Build the SELECT statement.

s = 

"SELECT DISTINCT " & b_id_table3 & " 

FROM " & aTABLE2 & " 

INNER JOIN " & bTABLE3 & " ON " & a_id_table2 & " = " & bid_table2 & " 

WHERE " & a_id_table2 & " IN (" & $myIDS & ") " ;

 

// Wrap the query in an ExecuteSQL call.

e = "ExecuteSQL ( s; Char ( 9 ) ; "|*|" )";

 

// Run the query.

result = Evaluate ( e );

 

 

-- Tim

[Fitch]

I think I messed around with this a few months back where I built the ? list and then used Evaluate to create the query -- but as you discovered, it amounts to pretty much the same thing. I don't know how to answer your question why -- that's just the way it works, the query parameters don't handle lists that way.

Edited May 9, 2014 by Fitch
Tim's answer is what I mean. Nice!

[James Gill]

Ok, now that we've got THAT figured out, onto another question.

I've discovered that this query seems to be being performed as an 'OR' query, and not as an inclusive 'AND' query.  For example, say I want to perform a find for records that contain A, B, C and D

 

Record 1 = 'A, B, C, D'

Record 2 = 'A, B, C, D'

Record 3 = 'B, C, D'

Record 4 = 'B, C'

 

If I perform that find, I would EXPECT the 'IN' function within ExecuteSQL to return only Records 1 & 2, however it seems to be returning all records that contain A or B or C or OR D.  Unfortunately for me, I just read up on the 'IN' operator and this seems to be functioning as expected.

How would I formulate a query that returns only records that contain ALL of the values instead of any of the values?