Jump to content
SRayNoriega

Sending Variable to WHERE IN in ExecuteSQL Not Working

Recommended Posts

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 by SRayNoriega

Share this post


Link to post
Share on other sites

Hi,

This may help you

Good luck

Gerard

 

SQL 5.pdf

Share this post


Link to post
Share on other sites

And this to succeed

 

Capture d’écran 2017-10-12 à 18.16.49.png

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

Share this post


Link to post
Share on other sites
10 hours ago, LAIRY said:

And this to succeed

 

Capture d’écran 2017-10-12 à 18.16.49.png

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 by SRayNoriega

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now


×

Important Information

By using this site, you agree to our Terms of Use.