ExecuteSQL GROUP BY and HAVING clauses issue

[alonjr]

Hello, first, I'm a newbie.

 

SELECT t.fk_ProductID, MAX(t.EffectiveDate) AS latestDate

FROM Transactions t

GROUP BY t.fk_ProductID

HAVING t.fk_ContactID = '9EE273FD-A35C-4177-8E2F-8ABE6F368286'

 

The green part of the query above works well, however when I add the HAVING clause, it breaks with the following error:

 

"All non-aggregated column references in the SELECT list and HAVING clause must be in the GROUP BY clause."

How do I overcome this apparent limitation?

Looking forward to your feedback. thanks a lot.

[comment]

Do you really need to use HAVING here? I cannot see your data, but I would imagine that ContactID is an attribute of a record in Transaction, not of a group. So I don't see why WHERE would not work for you.

 

[alonjr]

WHERE was the first thing I tried because I thought that using GROUP BY requires using HAVING instead of WHERE.

Substituting HAVING with WHERE gives a syntax error.

Edited May 4, 2020 by alonjr
clarification

[comment]

It works for me - see the attached demo. How is your situation different?

TestSQL.fmp12

[alonjr]

@comment, thank you so much for creating a small demo, I'm very grateful!

Your demo indeed works; however i must apply the WHERE clause after the GROUP BY clause, otherwise I get the wrong results.

There must be another way to achieve it, I just don't know how.

[comment]

17 minutes ago, alonjr said:

There must be another way to achieve it

Possibly, but we won't know how until you explain exactly what "it" is. 

--
Technically, you could do what your error message suggests, and change:

GROUP BY t.fk_ProductID

to:

GROUP BY t.fk_ProductID, t.fk_ContactID

That should get rid of the syntax error - but I am not sure what meaning (if any) the result would have.

 

Edited May 4, 2020 by comment

[alonjr]

@Comment, Although I no longer get an error, adding t.fk_ContactID, I get the wrong result.

What I'd like to do, in essence, is

1. run the first green query below, then on the result
2. run the purple query below.

 

SELECT t.fk_ProductID, MAX(t.EffectiveDate) AS latestDate

FROM Transactions t

GROUP BY t.fk_ProductID

 

WHERE t.fk_ContactID = '9EE273FD-A35C-4177-8E2F-8ABE6F368286'

 

I am pretty sure that in the standard SQL, this is possible, but in Filemaker for some reason it is not permissible.

[comment]

2 minutes ago, alonjr said:

1. run the first green query below, then on the result
2. run the purple query below.

I am afraid that makes no sense to me. Please explain in simple terms what are you trying to accomplish.

 

[alonjr]

Explained as simple as possible

• Objective: track the whereabouts of items (Products) as they move from person (Contact) to person.
• At any given time, a Product is in possession of a Contact.
• Three tables in use, Products, Contacts and Transactions.
• A row in the Transaction table records:
  • t.fk_ContactID— the Contact that is in possession of ...
  • t.fk_ProductID— the Product and the ...
  • t.EffectiveDate— date-time at which the Contact took possession of the Product.

I want to know what Products Steve (a specific Contact) has at this very moment.

To answer this question, I need to logically follow these 3 steps, in order:

1. Get a list of all Products that were at the possession of Steve (in the past and until now).
2. Identify the latest Transaction for each Product in the list of #1 above.
3. Filter out transactions, found in #2 above, that don't have Steve as the Contact

After step 3, you end up with a list of transactions that are associated with Steve and the Products that he is now in possession.

Hope this makes sense.

Edited May 4, 2020 by alonjr
grammer

[comment]

Yes, it is much clearer now.

I am not sure how this can be done using ExecuteSQL(). I am quite sure that the HAVING clause requires a condition that refers to an aggregate value. It would be easy if Filemaker's SQL supported a LAST() aggregate function; without it, I suspect this is much more difficult - if possible at all with the limited subset of SQL supported by Filemaker.

However, I can tell you how you can achieve this using native Filemaker methods:

1. Find the records you want to include in this query*;
2. Sort the found set by ProductID, and by EffectiveDate, descending;
3. Go to the first record and loop:
   • If the ContactID is not Steve, omit the current product group;
   • Else jump to the first record of next product group.

In the end you will have a found set of the groups where Steve is the most recent contact, and you can display them using a sub-summary report (with no body part). Alternatively, you could just loop over the groups and collect the ProductIDs of those where Steve is the contact in the first record.

In order to omit all records of a group and/or jump to the first record of next group, you will need a summary field that counts the records. Then use the expression:

GetSummary ( sCount ; ProductID )

to get the count of records in the current product group.

---
(*) To find transactions related to any products that were owned by Steve, you could start from Steve's record in Contacts and do GTRR to Products. From there do another GTRR to Transactions, this time matching the entire found set.

 

[comment]

Another option:

Add a calculation field to the Products table =

Last ( Transactions::ContactID )

then search this field. This is assuming that the relationship is unsorted and records are entered in chronological order, or that the relationship is sorted by EffectiveDate, ascending.

 

[alonjr]

@comment, thank you. I'm going to tinker with Filemaker relationships to see if i can achieve the non-ExecuteSQL alternative.

[alonjr]

@comment,

I finally found a solution which is a hybrid of a calculation field (as you pointed out in your last post above) and an ExecuteSQL query functioning as a record filter on the Portal.

Thanks so so much!!!