1 Reply Latest reply on May 26, 2010 11:37 AM by bvoth

    SQL Script



      SQL Script


      Hello all.  I am using FMPRO 11 for Windows, talking to an Oracle database via ODBC.


      I have a script that builds a SQL call:


      "SELECT * FROM prpsl."&Events::Short Beamline Name&"_prpsl_V where RUN_CYCLE_ID='"&Beamline Info::Run Cycle&"' and PRPSL_ID not in (select PRPSL_ID from <FileMaker Pro Table Name>  where RUN_CYCLE_ID='"&Beamline Info::Run Cycle&"')"


      The <FileMaker Pro Table Name> is tha part causing me trouble.  I have a FM table named Events, which has a field PRPSL_ID.   I am trying to say:  Select PRPSL_ID from the Oracle database if PRPSL_ID does not appear in the Events::PRPSL_ID.  The problem is how do I specify the table name Events?  I have tried


      (select PRPSL_ID from Events...           but that throws an error.


      Any suggestions?

        • 1. Re: SQL Script
          You are trying to include your FMP table in the SQL call and it is not able to be used in that manner. I would probably create a way to "get" the PRPSL_ID from your FMP table and push into a variable (comma-delimited) then call the variable. Because I don't know your "context", you'll probably have to FIND the records you want first and loop through them setting the variable (appending with each iteration). I'd also test for empty variable, before making the actual SQL call