1 2 Previous Next 15 Replies Latest reply on Sep 19, 2012 10:42 AM by Greg

    What syntax do I use to ExecuteSQL in a FM script against an Oracle ODBC source?

    markjulie

      My enterprise Oracle ODBC source is already setup as a DSN and properly shows Oracle data in shadow tables that have a 1:1 relationship in my FMDB. I have created a unique FMPA record for each distinct primary key combo of ACCT_NO and FCILTY_ID. Those records are created by importing all records from an Oracle sandbox table.

       

      After creating a record, I then need all ICD9 codes associated with that record (anywhere from 1-50 codes). I used this ExecuteSQL query which returns '?' in the calculated field. I have also tried INNER JOIN with similar results.

       

      ExecuteSQL (
      "SELECT

      e::ICD9_DX_CD

      FROM CM.ENCNTR_ICD9_DX e
      LEFT JOIN StrokeGWTG s
      ON s::ACCT_NO = e::ACCT_NO

      AND s::FCILTY_ID=e::FCILTY_ID

      WHERE e::ICD9_DX_SEQ=1";",";"¶")

       

      Can this query work, and how should I modify it?

       

      Mark

        • 2. Re: What syntax do I use to ExecuteSQL in a FM script against an Oracle ODBC source?
          Oliver_Reid

          Of course it would save a ton of time if ExcuteSql simply interpreted   :: as  .   --   is there a reason why that would not be do-able?

          • 3. Re: What syntax do I use to ExecuteSQL in a FM script against an Oracle ODBC source?
            beverly

            I don't know for sure what you have that are valid table and field names, but try this.

             

            "SELECT e.ICD9_DX_CD

            FROM \"CM.ENCNTR_ICD9_DX\" e
            LEFT JOIN StrokeGWTG s
            ON s.ACCT_NO = e.ACCT_NO

            AND s.FCILTY_ID=e.FCILTY_ID

            WHERE e.ICD9_DX_SEQ = 1"

             

            the "alias" of the table name is used in SQL with the "." dot. If you did not create the alias, the full name would be used, for example:

                   StrokeGWTG.ACCT_NO

             

            the dot in the FROM table name may or may not cause errors, so I placed it in escaped quotes.

             

            Beverly

            • 4. Re: What syntax do I use to ExecuteSQL in a FM script against an Oracle ODBC source?
              beverly

              LOL, it would be my preferece that FM use "." instead of "::", but I know that would be a nightmare to switch!! I understand that the selection of a field for the calc would bring in the <<tableO::fieldname>> and it would be nice if ExecuteSQL used it as if it were <<tableO.fieldname>>

               

              Beverly

              • 5. Re: What syntax do I use to ExecuteSQL in a FM script against an Oracle ODBC source?
                markjulie

                If I use your syntax with the aliases, I still get '?' for field contents and FMPA hangs if I run it in real time instead of just via script debugger. If I remove the aliases, the error is "table cannot be found."

                 

                If I use data viewer to evaluate an expression and enter the Execute SQL there, it hangs.

                 

                Does this sound like a solvable solution via trial and error over a day or two, or should I pursue the paid FM consultant route if a solution by the end of the week is desirable?

                • 6. Re: What syntax do I use to ExecuteSQL in a FM script against an Oracle ODBC source?
                  greglane

                  Hi Mark,

                   

                  If the ICD9 codes are coming from Oracle via ESS, I'd avoid using the ExecuteSQL function. Because of the way the ExecuteSQL function works with ESS data, you're likely to run into performance issues.

                   

                  In this case, I'd just create a FileMaker relationship between the two tables and use the List function to get the codes. It should perform better and it's easier to create and maintain.

                   

                  Greg

                  • 7. Re: What syntax do I use to ExecuteSQL in a FM script against an Oracle ODBC source?

                    I would have to agree with Greg. Using ExecuteSQL on ESS tables is going to be inefficient. It almost has to be double translated by FileMaker in that scenario. I would avoid it.

                    • 8. Re: What syntax do I use to ExecuteSQL in a FM script against an Oracle ODBC source?
                      beverly

                      + 1 with Greg and John. If you can set up views to perform the query, that would be preferable. If you cannot, because you are using FM tables and ESS shadow tables, can you just script a normal find on the table(s)?

                       

                      Beverly

                      • 9. Re: What syntax do I use to ExecuteSQL in a FM script against an Oracle ODBC source?
                        markjulie

                        Beverly, John, or Greg,

                         

                        I hear the consensus on avoiding ExecuteSQL in favor of using the shadows tables and ESS.

                         

                        Would anyone care to translate the SQL algorithm into the most suitable FM syntax (baby steps don't offend me)? My DSN and ESS shadows are setup. For simplicity, here are the relevant tables and fields:

                         

                        FM

                        Stroke::ACCT_NO

                        Stroke::FACILITY

                         

                        Oracle

                        CM.ENCOUNTER.ACCT_NO

                        CM.FACILITY

                        CM.CODE

                        CM.SEQUENCE

                         

                        The tables are joined on pk ACCT_NO and FACILITY. In Oracle, one ACCT_NO and FACILITY can have up to 50 records.

                         

                        What FM function will return and/or concatenate all CM.CODE values for a given ACCT_NO and FACILITY? I know how to take a string and apply RIGHT, MIDDLE, and LEFT to get substrings, but I don't know how to create that list/array in FM.

                         

                        Mark

                        • 10. Re: What syntax do I use to ExecuteSQL in a FM script against an Oracle ODBC source?
                          greglane

                          Mark,

                           

                          Create a relationship between the two table occurrences on the relationships graph. It sounds like you have a Stroke table and a CM table (which is coming from the ODBC data source). The relationship should have Stroke::ACTT_NO = CM::ENCOUNTER.ACCT_NO and Stroke::Facility = CM::FACILITY.

                           

                          Once the relationship is created, create a new calculation field in the Stroke table. The calculation would simply be:

                           

                            List( CM::CODE )

                           

                          Make sure the calculation has a Text result. That will give you a return-separated list of the related codes for each record in the Stroke table. If you prefer a comma-separated list, the Subsitute function can be used to replace each of the carriage returns with commas:

                           

                            Substitute( List( CM::CODE ); ¶; ", " )

                           

                          It isn't clear how you want to use the ICD9_DX_SEQ field. Is this field used for sorting the codes or are you filtering the records based on the value?

                           

                          Greg

                          • 11. Re: What syntax do I use to ExecuteSQL in a FM script against an Oracle ODBC source?
                            markjulie

                            Greg,

                             

                            The table occurrences and relationships already exist as prescribed. After creating the calculated field, List(CM::CODE), it remains blank on my layout but, probably due to the ODBC lag, takes about three minutes for the beach ball to quit.

                             

                            Frustrated and hoping for more direction. I cannot even get it to work if I pass in a unique ACCT_NO and FACILITY. BTW, the sequence field would be used to filter the List() returned results if it worked.

                             

                            Mark

                            • 12. Re: What syntax do I use to ExecuteSQL in a FM script against an Oracle ODBC source?

                              3 minutes seems like a really long time for 50 records or less. Are you using a list view?

                              • 13. Re: What syntax do I use to ExecuteSQL in a FM script against an Oracle ODBC source?
                                Greg

                                Good Day,

                                 

                                I may be missing something here but it is my understanding that the ExecuteSQL FUNCTION is for using SQL within FileMaker and that the Execute SQL SCRIPT STEP is to be used with ODBC data sources. 

                                 

                                There seems to be some confusion about this in this thread.

                                 

                                greg

                                • 14. Re: What syntax do I use to ExecuteSQL in a FM script against an Oracle ODBC source?
                                  beverly

                                  Yes Greg! a great deal of CONFUSION (not just you!). In a nutshell:

                                   

                                  1) Set up ODBC source to the approved MS SQL, MySQL or Oracle, using the approved drivers.

                                   

                                       a) use with IMPORT script step to pull data from SQL into a table in FileMaker. Uses SQL SELECT statement to get the data to pull. The connection of data is no longer "live" once it's imported. Changes in FM or SQL will not affect the other. Import can be used to "update matching records" in the dialog, but that's still not "live". And only works with client. NOTE: you may be able to use this with "unapproved (for ESS)" SQL and drivers.

                                   

                                       b) use Execute SQL script step to push data to SQL from FileMaker. Uses SQL INSERT, UPDATE & DELETE statements to make the push. The connection of data is no longer "live" once the script completes. Changes in FM or SQL will not affect the other. If you try to UPDATE or DELETE a SQL row based on a match in the WHERE clause, you will get an error if it no longer exists in the SQL. And only works with client.  NOTE: you may be able to use this with "unapproved (for ESS)" SQL and drivers.

                                   

                                       c) make a "shadow table" by adding the external SQL source table to the relationship graph.

                                            1. ESS = External SQL Source. This becomes more "filemaker-like" as you can find, add, edit, delete (with permission, of course!) the data in the SQL table and create relationships to, use in scripts and more. It's sort of a "live view" into the SQL. You may still have conflicts as there is no direct "TRANSACTION" step that is in FM, so once a change is made to the row and the change is committed, it changes! And ESS works with hosted FM files.

                                            2. you can also use the ExecuteSQL() function on the ESS table, as it's a reference on the graph like FM tables. YMMV!

                                   

                                  NOTE: before ESS, IMPORT and Execute SQL script steps were the only way to communicate with SQL directly from FM. And you had to really work hard to "sync" the two.

                                   

                                  2) Set up FileMaker sources to other FM tables in remote or local databases.

                                        

                                       use ExecuteSQL() function as defined on the FM tables (table occurrences or alias' as on the graph).

                                   

                                  Does that help?

                                   

                                  3) Oh yes, of course... a FileMaker database (and all its tables), can be an ODBC source for other programs.

                                   

                                  Beverly

                                  1 2 Previous Next