I'm having trouble using a variable with WHERE IN in FileMaker ExecuteSQL.

A bit about what I am trying to accomplish and my environment.  My company has heaps of content from hundreds of providers.  We use FileMaker to store the bibliographic data in an easy to use layout for internal end-users.  The data in FileMaker is imported from a csv file that is an export of a view from Microsoft SQL database.  The view contains all biblio data and relevant record IDs and is stored in a table called 'FM_EBookDataFields_And_Shadow_Fields' .

Sometimes ContentProviderA and ContentProviderX send the same content (there could be dozens of ContentProviders for X.)  I want to find all distinct PublisherName values from ContentProviderX where they match the distinct values from ContentProvider A.  The result  values need to be grouped and arranged in a particular format.

Here's what my script (kind of) looks like:

1. Set Variable: $CPID: Get the UID for ContentProviderA

2. Set Variable: $SourcePubList: an ExecuteSQL that finds distinct PublisherName values from ContentProviderA and then outputs them to a list that (I think) WHERE IN should be able to parse.  Here's what the results of $SourcePubList could look like:

('Acumen','Aldine Transaction','Apple Academic Press')

For the field separator, I am using Char(32).

2. Here's where I need help.  Set Field 'CpCrossMatch': Performs the following ExecuteSQL and set the results to 'ContentProviders::CpCrossMatch':

ExecuteSQL ( 
     "SELECT 
        e.CPID, 
        e.ContentProviderName, 
        e.PublisherName
      FROM FM_EBookDataFields_And_Shadow_Fields e
      WHERE e.PublisherName IN ? 
        AND e.CPID <> ?
        AND  ( e.WorkFlowstatus = ? 
          OR   e.WorkFlowStatus = ? )
      GROUP BY 
        e.CPID, 
        e.ContentProviderName, 
        e.PublisherName 
     "
 ; " : " ; "" ;  $SourcePubList  ; $CPID ;  "Received" ; "Live"
)

I can query the database directly with my SQL statement and it works just fine.  I've also tried running the above with and without the WHERE IN statement.  The WHERE IN is the trouble.

Does WHERE IN not support variables or am I doing something wrong?  

 

Edited October 11, 20178 yr by SRayNoriega

Hi,

This may help you

Good luck

Gerard

 

SQL 5.pdf

And this to succeed

 

Edited October 12, 20178 yr by LAIRY
To complte : from https://filemakerhacks.com/2012/10/19/the-missing-fm-12-executesql-reference/

10 hours ago, LAIRY said:

And this to succeed

 

Thanks for this comment.  I actually have checked countless forums and the SQL missing manual so, so many times. 

I didn't think the Let would treat the query any differently but it was worth a shot.  Unfortunately I get the same result using the Let method.  First I tried nesting my SELECT inside of WHERE IN but that resulted in a "?".  Next I tried using my $SourcePubList variable (the results of my previously directly inserted SELECT statement, except formatted with single-quotes and commas.) inside of WHERE IN.  Still no dice.

I'm curious how the example statement should function.  In a WHERE IN, shouldn't the values be presented in a single-quote, comma-delimited format?  If we do no formatting to the query results (i.e. field/row delimiter in ExecuteSQL) how does WHERE IN know how to treat each separate value?  If I tried to do that in a normal SQL environment, the interpreter would treat all of these values as one.  This concept blows my mind.

Edited October 13, 20178 yr by SRayNoriega

Hello,

The first pdf explains that in the « IN » order, with dynamic parameters, each item must be enumerated separately.

In our case, as Kevin Franck state in 2012 in The Missing FM 12 ExecuteSQL Reference

 « If the list is TEXT, then the values, must be enclosed with single quotes (‘abc’,’def’,’ghi’,’jkl’). These values will be automatically be quoted as needed, if you nest another SELECT inside. »

Before our exchanges I have tried in one of my database:

Definir variable $$liste ; valeur : Definir (

[sql = Substituer (  ExecuterSQL ( "SELECT SERVICE

FROM SERV_POLE

WHERE POLE = ? " ; "" ;""; "SARMU" ); "¶" ; "','")]  ;

"'"&sql&"'")

and I got : 'ANESTHESIE','MORTUAIRE','PRELEVEMENT D ORGANE','REANIMATION','SAMU','UCMJ','URGENCES'

With  ($$liste pasted ) :

ExecuterSQL ( "SELECT AVG(AGE)

FROM IDENTITES

WHERE SERVICE IN ('ANESTHESIE','MORTUAIRE','PRELEVEMENT D ORGANE','REANIMATION','SAMU','UCMJ','URGENCES')" ; "" ; "" )

And

ExecuterSQL ( "SELECT AVG(AGE)

FROM IDENTITES

WHERE SERVICE IN (SELECT SERVICE

FROM SERV_POLE

WHERE POLE = ?)" ; "" ; ""; "SARMU")

I had the same result : 41.907258064516129, but the second way to do is easiest  and elegant

Sorry, I don’t speak (and write) english fluently  but I hope this helps you.

Gérard

 

On 10/13/2017 at 11:26 AM, LAIRY said:

Hello,

 

The first pdf explains that in the « IN » order, with dynamic parameters, each item must be enumerated separately.

 

In our case, as Kevin Franck state in 2012 in The Missing FM 12 ExecuteSQL Reference

 

 « If the list is TEXT, then the values, must be enclosed with single quotes (‘abc’,’def’,’ghi’,’jkl’). These values will be automatically be quoted as needed, if you nest another SELECT inside. »

 

Before our exchanges I have tried in one of my database:

 

Definir variable $$liste ; valeur : Definir (

 

[sql = Substituer (  ExecuterSQL ( "SELECT SERVICE

 

FROM SERV_POLE

 

WHERE POLE = ? " ; "" ;""; "SARMU" ); "¶" ; "','")]  ;

 

"'"&sql&"'")

 

and I got : 'ANESTHESIE','MORTUAIRE','PRELEVEMENT D ORGANE','REANIMATION','SAMU','UCMJ','URGENCES'

 

 

With  ($$liste pasted ) :

 

ExecuterSQL ( "SELECT AVG(AGE)

 

FROM IDENTITES

 

WHERE SERVICE IN ('ANESTHESIE','MORTUAIRE','PRELEVEMENT D ORGANE','REANIMATION','SAMU','UCMJ','URGENCES')" ; "" ; "" )

 

And

 

ExecuterSQL ( "SELECT AVG(AGE)

 

FROM IDENTITES

 

WHERE SERVICE IN (SELECT SERVICE

 

FROM SERV_POLE

 

WHERE POLE = ?)" ; "" ; ""; "SARMU")

 

 

I had the same result : 41.907258064516129, but the second way to do is easiest  and elegant

 

Sorry, I don’t speak (and write) english fluently  but I hope this helps you.

 

Gérard

 

 

This did help, thanks!  I was able to understand even though it is not all in English.

So what was happening in my case is that my query seems to have been working while nesting SELECT within WHERE IN.  The problem was the function was taking too long to resolve and the script was timing out.

I was specifying my variables for Let as local variables ($) .  Based off of this I tried replacing them to be as global variables ($$) and the statement resolves 4 times faster.  In the former case my query took over 4 hours and then timed out; and in the latter it now completes in 1.5 hours.

Now I'm thinking this is perhaps not the quickest way to approach my solution but that is another topic.