Try using . instead of :: ?
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?
I don't know for sure what you have that are valid table and field names, but try this.
FROM \"CM.ENCNTR_ICD9_DX\" e
LEFT JOIN StrokeGWTG s
ON s.ACCT_NO = e.ACCT_NO
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:
the dot in the FROM table name may or may not cause errors, so I placed it in escaped quotes.
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>>
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?
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.
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.
+ 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, 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:
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.
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?
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.
3 minutes seems like a really long time for 50 records or less. Are you using a list view?
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.
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.