FM SQL ExecuteSQL syntax troubles for IN clause

[Justin Close]

I'm having a syntax problem getting the 'IN' clause to work in FM's ExecuteSQL statement.  I would like to use a '?' placeholder in the statement (just looks cleaner), but when I do it doesn't work.  I already have a string that contains the list of numbers (this is all numbers...except the fact that it is a string of numbers :)  ).  I want to use that variable in this statement.

Here's a WORKING example:

 

    Let ( [
        ids = "281,282,283";
        sq =
    "SELECT  ID_fk, sum(Amount)  FROM Payments  WHERE ID_fk  IN ( " & ids & " ) GROUP BY ID_fk"
    ] ; ExecuteSQL ( sq ; "" ; ""  ) )

But it's not using the '?' placeholder.  What I would like to do instead:

    Let ( [
        ids = "281,282,283";
        sq =
    "SELECT  ID_fk, sum(Amount)  FROM Payments  WHERE ID_fk  IN ( ? ) GROUP BY ID_fk"
    ] ; ExecuteSQL ( sq ; "" ; ""  ; ids ) )

 

I found some custom functions from folks that help format this correctly, but I would rather not use a custom function for just this one need (only doing this once).

Thanks,

--  Justin

[Kris M]

SELECT  ID_fk, sum(Amount)  FROM Payments  WHERE ID_fk  IN ( SELECT ( get ids here) ) GROUP BY ID_fk"

find ids as a select in the in clause should work

Edited June 10, 2015 by Kris M

[Justin Close]

Yeah, this would work; I saw folks giving examples similar to this.  But I already have the list of IDs; would be nice to not have to hit the DB again just to get them formatted correctly...whatever that format is for the '?' placeholder. 

 

[Wim Decorte]

AFAIK the ? parameter placeholder does not work for the IN clause.  You have to specify the data.

The biggest pain with that is that you have to know what data type the field is that you are searching on and take care of the proper quoting (text) and formating (dates, times).... things that FM takes care of with the ?

[Justin Close]

Well, that would explain why no one seems to show any examples of using the "?" placeholder in there.  Everything I have found is using sub-queries or concatenated FileMaker text/variables.

[morninger]

You need one placeholder per value, in the IN clause.

If you have 4 values, your query should be SELECT... IN (?,?,?,?)

If you don't know how many values you have to manage in advance, you need some coding for creating the placeholders and manage the arguments.

A bit tricky, but it can be done.

[eos]

If you don't know how many values you have to manage in advance, you need some coding for creating the placeholders and manage the arguments.

A bit tricky, but it can be done.

Question is: why would you want do it that way?

[Wim Decorte]

There is some value in using "?" mainly if you have dates or times or you're not entirely sure if the field is text or number.   With ? you can let FM do the quoting and "syntaxing".

It's fairly simple to abstract the "writing" of the ExecuteSQL statement to a script; I showed that in last year's devcon as part of a strategy to log all executeSQL() requests and how long they take.  Helps a lot in troubleshooting.

[Diver]

I have run into a similar situation trying to use the 'IN' clause but have not been able to find a resolution.  

I have two FM files.  The first one uses a sql call to capture a long list of part numbers (see example list below).  I then pass the results to a  second file in a script parameter.  

Example list: 
A12345
A85732
B45449
.... could be 1,000 items in the list

 

Here is what I have tried:

Let ( [ 
ReturnSub = "\n" ; // Swap out carriage returns in results with a different character.  \n is the default.
$itemIDs = Get(ScriptParmeter) ;
SQLResult = ExecuteSQL ( 
    "SELECT a.\"PartName\"
    FROM \"Inventory\" a
    WHERE a.\"PartNo\" IN ($itemIDs)" ; 
    "    " ; "|*|"
     ) ;
partNames = Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  )
] ; 
partNames
)

 

I have also tried using a global field (sqlSelect_g) in the second database and setting the passed parameter list into the field so it has a long list of items for the use by the SELECT clause :

Let ( [ 
ReturnSub = "\n" ; // Swap out carriage returns in results with a different character.  \n is the default.
SQLResult = ExecuteSQL ( 
    "SELECT a.\"PartName"
    FROM \"Inventory\" a
    WHERE a.\"PartNo\" IN (SELECT ( \"sqlSelect_g\" FROM \"Inventory\"))" ; 
    "    " ; "|*|"
     ) ;
partNames = Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  )
] ; 
partNames
)

 

I think I would be fine if I was in one file and could use the SELECT clause within the IN clause but the data I need for the IN clause is not from the file I need it for.

Any suggestions?

[Justin Close]

Diver,  2 things: 

First, your string of values must be in a specific format to function in the IN clause.  Something like "23, 432, 523".  If you are using strings in the values, they have to be quoted.  A return-separated list won't work.

Second, in your first example it doesn't look like you are correctly using the variable "$itemIDs" - you don't exit the FileMaker string-mode, so it is using the literal string "$itemIDs" and not variable replacement.  Here's what it should look like: 

"...IN ( " & $itemIDs & " ) ..."

 

[Diver]

Thanks Justin for the reply.  I have changed the string of values to be what you outlined, not return-separated.  I have my two original examples slightly modified trying what I thought your reply suggested yet neither work.  I am now trying do a sql query in all records in the INV__Inventory table and only return those numbers that are in the "IN" string. Here are the two calcs, along with their results.

 

Example 1:  

Though I am setting the $itemIDs, it is only being used to show what the field value of sqlSelect_g is.  It was my understanding that this method, using the "IN( SELECT...." is needed as Kris M noted above. 

Let ( [ 
$itemIDs = INV__Inventory::sqlSelect_g ;
SQLResult = ExecuteSQL ( 
    "SELECT a.\"PartNo\"
    FROM \"INV__Inventory\" a
    WHERE a.\"PartNo\" IN (SELECT ( \"sqlSelect_g\" FROM \"INV__Inventory\"))" ; 
    "    " ; "|*|" 
     ) 
] ; 
 "SQLResult is:     " & SQLResult & ¶ & 
"When $itemIDs is:   " & $itemIDs
)

----------- RESULT OF CALC ------------

SQLResult is:     ?
When $itemIDs is:   "0110015004, 0110015005, 0110015008"

 

 

Example 2:  

Here I am using the "IN" clause without the SELECT clause, corrected as you pointed out in your previous post.

Let ( [ 
$itemIDs = INV__Inventory::sqlSelect_g ;
SQLResult = ExecuteSQL ( 
    "SELECT a.\"PartNo\"
    FROM \"INV__Inventory\" a
    WHERE a.\"PartNo\" IN (" & $itemIDs & ")" ; 
    "    " ; "|*|" 
     ) 
] ; 
 "SQLResult is:     " & SQLResult & ¶ & 
"When $itemIDs is:   " & $itemIDs
)

----------- RESULT OF CALC ------------

SQLResult is:     ?
When $itemIDs is:   "0110015004, 0110015005, 0110015008"

[Diver]

Believe I found the solution.  The list has to look like this:        WHERE a.\"PartNo\" IN ('0110015004', '0110015005', '0110015008')" ;

Setting the variable $itemIDs to that same list and then using:     WHERE a.\"PartNo\" IN (" & $itemsID & ")" ;     does NOT work

Nor does setting a global field and using SELECT to pull the data from the global such as:     WHERE a.\"PartNo\" IN (SELECT ( \"sqlSelect_g\" FROM \"INV__Inventory\"))" ; 

Unless someone could provide a way to use a dynamic list of items to use with the 'IN' option I think the 'IN' option has a very narrow focus and use, though good were it will work.

[Wim Decorte]

You can use the "?" placeholder for the IN clause and have all the benefit of having FM automatically quote for the right data type etc.

Only caveat is that you need as many ?s as there are elements for the IN list.  So doing something like this:

 

WHERE a.PartNo IN (?,?,?,?) will work if you then pass in 4 elements to your ExecuteSQL() parameters.

To make this entirely dynamic, all you need to do is create a script to create the SQL syntax and use Evaluate() to execute the ExecuteSQL().  Or a recursive custom function to do this.  I prefer a script since I don't use ExecuteSQL() anywhere but in scripts.

[Diver]

Thanks Wim.  I sometimes forget about 'rolling your own'.   In this case it would have been ugly as there would have been over a 1,000 ?s.     A recursive would have worked if I did them one by one.

I stumbled across a Custom Function on briandunning.com that fit what I needed perfectly.  You simply give the CF a list and it does the rest for you.   

VLookup ( LookupTableAndFieldName ; MatchingValueOrList ; ReturnFieldNames )

[benmort81]

On 25/02/2017 at 12:37 AM, Diver said:

Believe I found the solution.  The list has to look like this:        WHERE a.\"PartNo\" IN ('0110015004', '0110015005', '0110015008')" ;

Setting the variable $itemIDs to that same list and then using:     WHERE a.\"PartNo\" IN (" & $itemsID & ")" ;     does NOT work

Nor does setting a global field and using SELECT to pull the data from the global such as:     WHERE a.\"PartNo\" IN (SELECT ( \"sqlSelect_g\" FROM \"INV__Inventory\"))" ; 

Unless someone could provide a way to use a dynamic list of items to use with the 'IN' option I think the 'IN' option has a very narrow focus and use, though good were it will work.

1

Hi Diver,

Manipulating the ~list into a format that works in sql, and then constructing the ~sql query string with the formatted list sandwiched, like  ...IN (" & ~here & ")"  worked for me:

Let ([
~List = TableA::Summary_List
~SubList = Substitute ( ~List; "¶" ; "','");
~here = "'" & ~SubList & "'";
~sql =
"SELECT Field FROM TableB
WHERE Field = ? AND FK_ID IN (" & ~here & ")";
];

ExecuteSQL ( ~sql; ""; ""; 1)

)

 

[neutralbay]

Hi,

I had the same problem and looks like I came up with nearly the same solution as benmort81.  Hope this helps someone. Once you see it, easy to use.

Let ( [
	/*
	this is what we're after... how many bid sheet items are in all found bid sheets (in this case, 2)
	example if what we need:
	bs_ids = "'02896204-2CE4-48DA-8F4F-16802A201F3C','E6D81F64-4340-488B-A6C1-92AA9DD92908'";
	result is 25, which is correct.
	*/

	// our IDs from a List function (return delimited)
	bs_ids = List ( scopes.sco_bid_sheets::id_bidsheet ) ; 
	
	// our IN values with casting the values as text
	bs_IN = "'" & Substitute ( bs_ids ; "¶" ; "','" ) & "'";

	// finally out SQL with our IN clause
    sql = "SELECT id FROM bid_sheet_items WHERE id_bid_sheet IN (" & bs_IN & ")" ;
    fs = "" ;
    rs = "" ; 
	result = ValueCount ( ExecuteSQL ( sql ; fs ; rs ; bs_ids ) )
    ] ;
    result
)

 

[Pure Fool]

Hi!

Try as I might, I have not been able to amend the Let functions in the last two posts to successfully work with:

WHERE ROWID IN (" & list_rowIDs & ")"

Has anyone else managed to do this?

Basically, I am endeavoring to write an SQL SELECT statement that operates on a found set (whose Record IDs are stored in an FM variable created via a looping script).

Thanks in advance for any pointers.

 

 

 

 

 

 

[comment]

What does the content of list_rowIDs look like? And if it's a variable created by a script, shouldn't it be $list_rowIDs or $$list_rowIDs?

 

[Pure Fool]

Hi!

Thanks for the speedy reply.

The original global field (text)  - generated by a looping script - is a list of Record IDs, separated by carriage returns.

910
911
912
913
914
915

I base the variable in my Let statement on this...

And, writing this, I realise what the issue I am experiencing probably centres around: I need a list of numeric values!

D'Oh!

Regards,
 

 

 

 

 

 

[comment]

19 minutes ago, Pure Fool said:

I need a list of numeric values!

You need a list of numeric values separated by a comma. Eventually, your query needs to look like:

WHERE ROWID IN (910,911,912,913,914,915)

 

 

Edited June 27, 2019 by comment

[Pure Fool]

Forgive me, but do you happen to know how to generate this?

Should I leave the original global field listing as text, then convert it?

Or is there a way to generate a numeric list separated by commas straight off?

Thanks again.

[comment]

Perhaps it would be better to open a new thread and start at the beginning?

[Pure Fool]

Okay. Will do. Thanks for the heads up.

Regards,