ExecuteSQL – Taming the Beast – Part 2

In Part 1, I wrote about  my first experiences with the ExecuteSQL function in Filemaker, looking at returning a basic list of field contents, passing arguments, and the use of simple joins.  The function is proving useful, and I’m delving further into it, with help from others.

Here are are my latest discoveries.  (It’s all rather random, i.e. I’m finding out more as and when I need it, so this may not appear to be a logical order in which to describe things.)

Putting the query in a variable

It seems to be common practice to put the query statement and its result into calculation variables.  (I’ve yet to see the full benefits of this, but enough experts do it to make me think it’s good practice!)

So, instead of:

ExecuteSQL ( “SELECT nameFirst FROM PPL WHERE nameLast = ‘Smith’ ” ; “” ; “” )

We get:

Let ([
$query =
“SELECT nameFirst, nameLast
FROM PPL
WHERE nameLast = ‘Smith'”;
$result = ExecuteSQL( $query ; “” ; “”)
];
$result)

Field and row separators

By default, if the function is returning more than one field (e.g. nameFirst and nameLast in the above example), these will be separated by a comma.  You can put any other separator as the second parameter of the function, e.g.

esql1

 

 

 

 

 

 

 

 

 

(See the effect of this in the “Result” pane above.)

And if you want to separate rows with something other than the default carriage return, just put that in as the third parameter, e.g.

esql2

 

 

 

 

 

 

 

 

 

“ORDER BY”

It won’t come as a great surprise that the ORDER BY clause in SQL enables you to sort the output.  The order can be ASCending or DESCending, but if you want the result in ascending order, you don’t need to specify it, as it’s the default.

esql3

 

 

 

 

 

 

 

 

 

“WHERE…IN”

This one’s a little bit fiddly, and I needed the generous help of members of the Filemaker TechNet to get it working.

I needed to limit the scope of the query.  I had already isolated a list of keys, via a combination of Filemaker find and eSQL query,  I had hoped originally to base the next eSQL query on the table occurrence in which these keys were listed.  Unfortunately, though, eSQL will always use the BASE TABLE of any table occurrence – it doesn’t recognise the filtering via relationships which are so useful elsewhere in Filemaker.

So I’ve got a list of keys of the records, in a global field, PPL::FILTERIDS.  I want to query within this filtered subset – “within the group of records that I’ve already found, tell me how many are women, how many have disabilities, etc.”.

To start with, I’ve just done a query to give me the people’s names, like this:

esql4

 

 

 

 

 

 

So we’re saying give me the names of each person whose id appears IN the list of IDs.  But we can’t just use the field itself, because that is a return-delimited list.  The IN clause needs a comma-separated list enclosed in parens, and, furthermore, each value in the list needs to be in (single) quotes (if they are alphanumeric text values).  So the setting of the ~idList includes replacing each CR with ‘,’.  Then, in the query variable itself we add an opening paren plus, and a closing plus paren.  Fiddly, but quite portable once you’ve got it working the first time (I hope!).

Note that it’s a bit less fiddly with numeric strings, in that they don’t need the single quotes around each value.

With this as a basis, we can carry on with more complex querying.  More about that in a later post.

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s