Where to use ExecuteSQL

[Rick Whitelaw]

I've recently been studying the ExecuteSQL function, and thus SQL as well. I've learned quite a lot and am able to write equations using dynamic WHERE clauses, JOIN etc. I find nesting the function in a Let is very efficient. Ok . . . Now my silly question. Where do I use this amazingly fast and flexible function? I mean where can I insert it in an existing solution. I can see where it would allow for the elimination of many TOs, but if the TOs exist to enable data input in a related table I can't see how SQL will help. Should I view ExecuteSQL as simply a function to make reporting more efficient? Does anyone out there have a classic use of the function that really made a big difference in a solution? Don't get me wrong. In my next solution I will make extensive use of SQL, but now I'm at a bit of a loss even though I've managed to use the function to return exactly what I set out to return.

Rick.

[Lee Smith]

Automatic message

This topic has been moved from "Calculation Engine (Define Fields)" to "FQL - Internal SQL".

[David Jondreau]

ExecuteSQL() doesn't do anything you can't already do in FileMaker but it does encourage developers to do things differently.

 

My "typical" use is the scripting case where I'd traditionally need to navigate to another layout to do a find to grab an ID and use that in the original layout. Say, to get a list of child IDs that I don't have a relationship for. Instead of the Go to Layout[], Enter Find Mode[], Set Field[], Set Field[], Perform Find[], Loop-Set Variable-go to Next Record-End Loop (or a custom function to grab a field from a found set) and all the overhead that goes with that (like suppressing script triggers, New Window[] to preserve a tab panel, etc) I can just use ExecuteSQL().

 

It also eliminates all the table occurrences that are not needed for layouts. Most of your auto-enter calcs for example.

 

Personally, I don't go too crazy with it. I rarely use aggregate functions (like SUM) and never use JOIN. I do play around with some of the more cutting edge stuff, like dynamic value lists and virtual list reporting, but that's another story...

[Wim Decorte]

 

 

It also eliminates all the table occurrences that are not needed for layouts. 

 

 

 

I'm using it extensively in exactly the way David describes.  The point he makes that I've quoted here is the main benefit for me.  It allows for more modular scripting and not having to create relationships just to get to the data.

 

There is definitely a performance cliff once you start using JOINs but for simple SELECTS performance is grea.

[Rick Whitelaw]

David, when you say you don't use JOIN do you mean you use implicit joins, or that you use the function in one table at a time?

Rick.

[David Jondreau]

I'm not sure what an implicit join is.

 

I don't use the word JOIN in my ExSQL() statements.

[Rick Whitelaw]

Thanks David, but what I'm getting at is how do you access data in another table if you don't use JOIN?

[David Jondreau]

Can you give me an example of what you mean?

 

Then I can give you an example of how I would do it (or possibly say I would never do that).

[jbante]

David, for example, suppose we have the tables:

 

Person -< Relationship >- Person__associate

 

Given that you already have an id_Person, how would you write the ExecuteSQL to get a list of that person's associates' names and each associates' relationship to the first person?

[Rick Whitelaw]

Jeremy,

Exactly. I use JOIN for this sort of thing. I was just curious how this could be done without JOIN. My understanding is that implicit joins are deprecated now . . . In SQL at any rate.

Rick.

[David Jondreau]

I probably wouldn't use ExSQL() here, but rather old-fashioned relationships and List().

 

Possibly, I would have a calc field in the join table which is pulling the name of the associate and use ExSQL() on the join.

 

Maybe, just maybe, I would use two ExSQL()s, one to grab the associate IDs from the join, the other to grab the names from the associate.

[Rick Whitelaw]

Hi David,

 

When you say you don't use the word JOIN in ExecuteSQL I believe I now know what you mean. The following expression:

 

 

Let ([$SN=GetField ( Get ( LayoutTableName )&"::Short_Name" ) ; $query= "SELECT b.CELLPHONE, a.City

FROM Musicians AS a

JOIN CELL_PHONE AS b

ON a.MUS_NUMKEY=b.NUMKEY

WHERE a.Short_Name=?" ;

$result=

ExecuteSQL ($query

;" ";¶;$SN)];$result)

 

 Returns exactly the same result(s) as:

 

 

Let ([$SN=GetField ( Get ( LayoutTableName )&"::Short_Name" ) ; $query= "SELECT b.CELLPHONE, a.City

FROM Musicians AS a,CELL_PHONE AS b

WHERE a.MUS_NUMKEY=b.NUMKEY AND a.Short_Name=?" ;

$result=

ExecuteSQL ($query

;" ";¶;$SN)];$result)

 

This is what I mean by an "implicit" join.

 

Rick.

[Wim Decorte]

David, for example, suppose we have the tables:

 

Person -< Relationship >- Person__associate

 

Given that you already have an id_Person, how would you write the ExecuteSQL to get a list of that person's associates' names and each associates' relationship to the first person?

SELECT name, relationship FROM Person_associate WHERE fk_id_person = id_person

No need for a join in this scenario: all the fields you want to extract are from the Person_associate table and you are passing in "id_person" as a given value.  In my example fk_id_person is the foreign key that matches id_person in the Person_associate table

[jbante]

SELECT name, relationship FROM Person_associate WHERE fk_id_person = id_person

No need for a join in this scenario: all the fields you want to extract are from the Person_associate table and you are passing in "id_person" as a given value.  In my example fk_id_person is the foreign key that matches id_person in the Person_associate table

 

 

I think you misunderstand the data structure I meant to imply. The "relationship" field would be in the Relationship table, not Person__associate (another table occurrence of Person). The explicit join might be something along the lines of:

ExecuteSQL ( "SELECT p.name, r.relationship FROM Relationship AS r INNER JOIN Person AS p ON r.id_Person__associate = p.id WHERE r.id_Person = ?" ; "" ; "" ; Person::id )