Jump to content

SQL Where not working with Aggregate function?


This topic is 2576 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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 ).

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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! 

Link to comment
Share on other sites

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.)

Link to comment
Share on other sites

(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

Link to comment
Share on other sites

This topic is 2576 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Similar Content

    • By Will_Logic
      Hi, I have been using Base Elements command BE_FileMakerSQL in FileMaker script to SQL select data, and return with '<c>' as field(column) delimiter, and '<r>' as the row delimiter. The below line worked before, I dumped a whole FM database with these delimiters showing in text files. But somehow, now if I run below line, it seems to have changed to ?only allow? 1 character for the delimiter, which of course doesn't make sense anyway with varied characters in the data:
      BE_FileMakerSQL ( $sql_select;  "<c>" ; "<r>" ; $fm_file ) The string this returns now looks like for example: 352265<gwgwh<474848<<wegwgw<65755<gwgw ....
      i.e. it has only taken the first character of the delimiter, I changed delimiter string to test with eg "A≠" instead of "<c>", same problem, it then only inserts "A" as delimiter. Just wonder if anyone can guess what might have happened here?
      Thanks kindly any suggestions!
    • By Will_Logic
      Hi, I am using the Base Elements plugin Execute SQL command to select SQL on a table in another FileMaker file. For a file with many fields and quite a lot data in some fields, this times as about 1 minute just for SQL SELECT command for 1,000 rows. I haven't used MBS (monkeybread software) plugin yet, but I wonder if anyone knows if it is significantly faster?
      I.e. BE_FileMakerSQL vs. MBS(FM.SQL.Execute..) - is one significantly faster?
      Here's the code to select 1,000 rows for reference, $timer_seconds comes as about 60 seconds here. If I export same table internally in FileMaker using the menu export to Excel, it takes 29 minutes for all 41,000 rows, so timing seems similar for the Base Elements SQL. (NB there is some flag going on here in the FileMaker database so internal export gets 41,000 rows but script SQL SELECT COUNT(*) gets 200,000 rows for same table, but I don't think that is relevant to my question).
       
      Set Variable [$time1; Value: Get(CurrentTimeUTCMilliseconds)] Set Variable [$part_ca; Value: BE_FileMakerSQL ( "SELECT * FROM Contact_ACTIVITY OFFSET 100000 ROWS FETCH FIRST 1000 ROWS ONLY" ; "&≠≠&" ; "EOR≠≠EOR" ; "DataContacts11" )] Set Variable [$timer_seconds; Value: (Get(CurrentTimeUTCMilliseconds) - $time1) / 1000] MBS command I think would be something like:
      MBS( "FM.SQL.Execute"; "DataContacts11"; "SELECT * FROM Contact_ACTIVITY OFFSET 100000 ROWS FETCH FIRST 1000 ROWS ONLY" ; ...delimiters here I'm assuming.. ) We only have base FileMaker here on machine I am using, which can run the Base Elements plugin, but to run MBS plugin, needs FileMaker Pro Advanced to install it I think, am getting several databases in roughly same FM format where script workspace disabled, hence need to use SQL select run from script in my own external FileMaker file. Might consider getting FMP Pro Advanced if MBS is faster.
      Tx any suggestions!
    • By Peter Barfield
      Well, I'm new to SQL and have used it in it's basic select  mode. 
      Now I have been asked too create a dashboard using data from different tables to display. Which on the most part is OK and working great.
      I have had a request for a top customer list for the month and previous month (say best 5 ranked from 1-5.) and not together so 2 seperate views
      I am able to pull customers and SUM totals (seperately)for the month using SELECT DISTINCT I am able to get a list of the customers for the month. How would you then get a total or SUM of the invoice amounts for the given month with the customer name and their total ? also is there a similar function to FRACTION in filemaker?
      Listed is my SQL Statement that just returns a ?
      Which I understand to mean a syntax error however, being a SQL "Virgin" any halp would be appreciated. I know I am able to do this natively through filemaker however, as I said this is for a dashboard that uses data from a number of tables.
      Here is my calc that is obviously wrong.
       
      Currently working with Filemaker 15 Advanced.
       
      Let ( [ 
      ReturnSub = "\n" ; 
      SQLResult = ExecuteSQL ( 
      "SELECT DISTINCT (a.\"Customer Name\"), SUM(a.\"InvoiceTotal\")
      FROM \"Invoices \" a
      WHERE a.\"Invoice Sub Total\" > ? AND a.\"InvoiceMonth\" = ? AND a.\"InvoiceYear\" = ? AND a.\"Job Status\" <> ?" 
      GROUP ON a.\"Customer Name\" ASC; 
      "    " ; "|*|" ; 

       "0" ; Month(Get(CurrentDate))-1 ; Year(Get(CurrentDate)); "Giveaway"

       ) ] ; 
      Substitute ( SQLResult ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  )

      )
    • By sal88
      Hi all
      Apologies in advance if this is a vague question, or if it's been extensively documented, but I could do with some basic advice on moving filemaker tables to SQL. In terms of database development, I have only really known FileMaker.
      We have a 60 table, 25 user FileMaker database which takes care of most if not all of the company's operations. 
      There is now talk of allowing other platforms (such as PowerApps/PowerBI) access to the data. One such reason is other people are interested in making apps/applets that serve specific functions. This would be simple if the data were held on a SQL database.
      So if all the FileMaker tables were to be moved to SQL, what sort of a task is that in terms of keeping the existing FileMaker gui functioning?
      Or. is there a simpler way to grant other platforms access to filemaker tables?
      And of course, what are the downsides?
      Thanks
    • By schotja
      Looking for someone to help setup a simple connection to a sql database on cpanel for our Ranch to connect to cattle listings on our website..  I'll be using fmp12 server or content for now just establishing a connection via fmp12 client on windows 7 pro machines.
      Specifically I am having trouble establishing a DSN connection from any of my machines and have opened ports / given access in cpanel to my static IP.
      I have also updated MS ODBC drivers on one of the machines to see if that made a difference.  It may be something quick for an expert with experience or it might end up being a gremlin.
      I have set up some test databases for troubleshooting purposes and willing to do alot of the legwork if i'm able but also willing to pay someone for their expertise as well.
      thx.
  • Who Viewed the Topic

    2 members have viewed this topic:
    Daemon  Lola 
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.