,

Best of FileMaker 13 SQL- New Clauses for ExecuteSQL

⋅
FileMaker 13 added over 50 new features, including some obscure, but important features the will help most developers.  of Seedcode takes an detailed look at two new ExecuteSQL clauses.

One of the less obvious, but exciting new features in FileMaker 13 is the two new clauses available in FileMaker’s SQL syntax.  It’s less obvious, because you need to read the new FileMaker SQL Reference Guide to see they’ve been added.

(Here is the FileMaker 13 SQL Reference. All FM13 docs are here.)

The new clauses are OFFSET and FETCH FIRST.  We’ve been asking for these functions in the FMSQL engine since the plug-in days, and they’re finally here.  They give you the ability to slice your results up in different ways right within the query, as well as improve performance in certain situations.

OFFSET n [ ROW | ROWS ]

Offset is simple.  It allows you to skip a designated number of rows and display your results from there.  Consider the query:

SELECT ”CompanyName”
FROM ”Companies”

with the results:

Anderson Co.
Bullet Co.H
Hemmley Inc.
Bongo Co. (Smith, Jim)
Jameson, Inc.
Bethson, Dale 
James, Maggie
Firestat Co.
Marteson Environmental
Rankle Industries
Aranato, Shuji
Mitle, Todd
Jermaine, Dwight
Henn, Barbara
Hellern, David
Roberts, Robert

by applying the OFFSET clause we can designate an “artificial” starting point:

SELECT ”CompanyName”
FROM ”Companies”
OFFSET 8 ROWS

now returns:

 

via Best of FileMaker 13 SQL- New Clauses for ExecuteSQL.

Liked Liked
Need FileMaker Development Help? Or to purchase FileMaker Software?
Contact FM Pro Gurus for help