Jump to content

Simple Join


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

Recommended Posts

This seems so straightforward, and I keep ending up in the same place.

I have 2 tables, "therapists" and "years". They are joined in the relationship graph by years::therapistid = therapists::zk_TherapistID

The years table has the numeric fields "year" and "expectedrefsind"

I have a database field in therapists::TotalExpectedRefs, it's defined by this calculation . . . 

ExecuteSQL ( "

   SELECT SUM(Y.expectedrefsind) from years Y
   JOIN therapists T
   WHERE Y.therapistid = ?
   AND year > ?

" ; "" ; "" ; "T.zk_TherapistID" ;  2014 )

 

Expecting this to return a Number instead of a ?.  

Any help would be really appreciated.

 

P.S. I just converted these to fmp12 files with Filemaker16. They were previously fmp7 (originally much older than that), and I'm pretty sure there has never been ExecuteSQL used anywhere in the file.

Edited by jimlongo
Link to comment
Share on other sites

SQL has reserved words , and year is one of them. You should escape it, \"year\" .  I wouldn't use ESQL in a calc field actually. You could achieve this using a relationship. What does the data look like in the years table? Could this be a sub summary report?

Link to comment
Share on other sites

Hi Jim,

I agree with Barbara's observations that "YEAR" is a reserved SQL word, and that ExecuteSQL isn't generally a good fit for a calculated field.

If you insist however, I think this would work as a calculated field in THERAPISTS -- and of course if you have FMP Advanced, you can test this via the Data Viewer

ExecuteSQL ( "

   SELECT SUM( expectedrefsind ) 
   FROM YEARS 
   WHERE therapistid  = ? 
      AND \"year\" > ?

" ; "" ; "" ; therapists::zk_TherapistID ;  2014 )

 

You don't need to use the JOIN operator because both the fields referenced in the WHERE clause live in the YEARS table.

Hope this helps,

Kevin

Link to comment
Share on other sites

Barbara and Kevin are correct. Note that Kevin did NOT use a JOIN construct. If you have the value of

therapists::zk_TherapistID

as the field directly or set as a variable before calling the query, then you don't actually need the JOIN. However if you are passing a direct value in the parameter, it must have context. So presumably you are IN thrapists table (or layout based on a TO with therapists as the base table) to make the query, even though you ask for a sum from another table. Passing the parameter as a variable, however, you can be in any context and the query will work.

HTH,

Beverly

Link to comment
Share on other sites

Gee thanks everyone.  

I am in therapists context.

I may have tried what Kevin showed above earlier in my attempts, but as you all noted, not escaping \"year\" pretty much doomed all my ideas.

I'll see if a sub summary will also work.

 

Link to comment
Share on other sites

This topic is 2308 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
×
×
  • Create New...

Important Information

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