SQL Where not working with Aggregate function?

[MSPJ]

Hi -

I have two tables, Visits and Members. A particular member will have many visits, so there is a primary key in Member :  Member ID and a matching foreign key in Visits.  Each visit has a field which contains an expense. (OOP Amnt Copy).

 

I'd like to get the sum of all the visit expenses for each member, put it in a field for that member and show this in a portal of Members.

 

I'm using the ExecuteSQL function.

 

If I do this:

 

ExecuteSQL("Select Sum (Visits."OOP Amnt Copy") From "Family Member" Left Outer Join Visits on "Family Member"."Member ID" = Visits."Member ID"";"";"")

 

I get the sum across ALL members of all the visits  - not  individual sums for each member.

 

So I tried Where to do a find of all the matching visits for each member:

 

ExecuteSQL("Select Sum("OOP Amnt Copy") from Visits where "Family Member"."Member ID" = Visits."Member ID"";"";"")

 

THis gets an evaluation error  - ?

 

But then, if I replace Family Member.Member ID with an actual value -- 'M001' - it works for that member.

 

ExecuteSQL("Select Sum (Visits."OOP Amnt Copy") From  Visits Where  Visits."Member ID" = 'M001'";"";"")

 

 

So shouldn't my second function do the same thing as the last one, but with the calculation for each member being replaced by their member ID?  

 

Thanks!

Michael

[eos]

So shouldn't my second function do the same thing as the last one, but with the calculation for each member being replaced by their member ID?  

 

Thanks!

Michael

 

It should, but …

 

1. You cannot (and don't need to) use fully qualified FileMaker field references within the literal SELECT string. (Different from the fully qualified references with tables aliases that you use in a JOIN.) 

 

2. You got the quotes for your text key wrong (hence the evaluation error); you can put them in manually, but the easier way I think is using the parameter option of ExecuteSQL() which will handle the quoting behind the scenes, e.g.

ExecuteSQL ( "SELECT Sum ("OOP Amnt Copy") FROM Visits WHERE "Member ID" = ? " ; "" ; "" ; Visits::Member ID )

OTOH, for a simple aggregate function via an already existing relationship via primary/foreign keys, you could just use the appropriate FileMaker function: Sum ( Visits::OOP Amnt Copy ).

[MSPJ]

Hi - Thanks for your response.  However, I don't think I see what you mean...the quotes seem fine, couldn't find any errors.  And not sure what you mean about the fully qualified field references.  Would you mind writing it the way you think I should have?

 

THanks!

 

PS- yes, I know I could do this without the SQL, but I want to do more complex things and trying to test it out.

[eos]

And not sure what you mean about the fully qualified field references.  Would you mind writing it the way you think I should have?

 

I did; see the code example in the box.

[MSPJ]

Oh sorry - I did see the code, but it didn't seem like it really addressed the issue - but I just tried it and you are indeed right.  Unfortunately, I don't know why that code works but the other does not.  But thank you very much! 

[eos]

Unfortunately, I don't know why that code works but the other does not.

 

Because you correctly single-quoted the literal value in one version, but not the reference in the other one.

 

You could also combine literal string and FileMaker references:

 

ExecuteSQL ( "

SELECT SUM ( someFieldName )

FROM someTableName

WHERE someOtherFieldName = '" & 

Visits::Member ID & "'" ; 

"" ; "" )

 

As I said, it's easier to use the optional parameters of ExecuteSQL() and let the function handle these details. (And if you want to use eSQL() frequently, maybe use field names you don't need to quote – no blanks, special characters or leading underscores etc.)

[Wim Decorte]

(And if you want to use eSQL() frequently, maybe use field names you don't need to quote – no blanks, special characters or leading underscores etc.)

 

 

I take the opposite view and always quote tables and fields.  Apart from the reasons you specify, there are also SQL reserved keywords to be woried about and some of them are not obvious (like "Grant" ).

 

By using a CF so that it is easy to achieve two important goals:

- quote everything

- use real field references instead hard coded field names