i would suggest that the ? is being generated by MONTH(Date_Created)=? because FileMaker dates are not equal to SQL dates.. The cast operator might help but i make no guarantees.
"Date & Time functions that work well in ExecuteSQL() and may be used with the System functions, above or your date fields or even the properly formatted Date/Time text (in single quotes ‘YYYY-MM-DD hh:mm:ss’):"
From the SQL reference:
"The FileMaker Pro ExecuteSQL function accepts only the SQL-92 syntax ISO date and time formats with no braces."
farther down it says...
So i am guessing that if MONTH() is fed anything but a SQL-92 std DATE it will throw ?
Ok, let scrap that Idea. Going the relationship route How would I get this done?
Nice reply, co!
I cannot test, but if you put EvaluationError() around your ExecuteSQL(), you may get the proper error code.
OTOH, the aggregate in the WHERE clause, likely is the problem (or part of it). Try HAVING instead. And you may also need the GROUP BY (placed above the HAVING) clause.
Sent from miPhone
1 of 1 people found this helpful
What type of result does the c_Resolved_Elapsed field return? FileMaker won't apply the SQL AVG function to a time field. FileMaker Pro 16 Advanced's Data Viewer would show the following error if that's the case:
Parameter number 1 to the function "AVG" is not of the correct type.
You could change the result type to a number and your query would likely work.
c_Resolved_Elapsed is a timestamp field. The funny thing is I have the average listed as a Leading Grand Summary on a table view I just want to put this average value in a field. But yeah its a timestamp
I was able to get it to work by doing something different. I had created two fields:
1. c_ResolvedCritical -> ServiceMGMT::Priority = "Critical" and c_Month (another field created a while ago)
2.c_ResolvedCriticalAvg -> Avg (c_ResolvedCritical)
3 of 3 people found this helpful
If you're doing SQL .... You should be using a real SQL tool!
Check out RazorSQL as one example as it will do so much more than just get rid of the ridiculous and unhelpful ... ?
Often the error isn't at all what you thought.
Had you been using a real SQL Tool (or FMP 16), you would have seen the error right away and not been pulling your hair out and wasting your time. I don't even consider using FMP and SQL without a separate tool. I am hoping that FMP 17 or FMP 18 will be better in basic SQL capabilities beyond just showing an error message.
ERROR REPORTED FROM SQL TOOL:  [FileMaker][FileMaker JDBC] FQL0021/(1:7): Parameter number 1 to the function "AVG" is not of the correct type.
You can't average a timestamp field: It doesn't make sense.
If I add a number field, called "number", then AVG works fine:
The answer to how you do what you want to do is to calculate, say, the amount of time in minutes, seconds, or whatever something took. So, for example, a possible good approach would be to have a starting TIMESTAMP and an ending TIMESTAMP. Then, you take the difference of these possibly in a calculated field. Then take the AVG of the calculated field using those numeric timestamp difference values.
Be careful, though, since (amazingly) FMP doesn't seem to understand its own TIMESTAMP data type with other FMP functions. You may need an intermediate CF for that conversion just to do the date math.
HOPE THIS HELPS.
FM does auto type conversion when need, SQL doesn't.
Saying that, but FMSQL return local date format when concatenated with empty string.
in this calculation dateOrTimestamp is converted to formatted text...
I think you're missing the basic point: it doesn't make sense to average a Timestamp.
That's the logical error here; it's not SQL vs. FMP thing.
"timestamp" is really a number (of elapsed time), but if the user has chosen to return time/timestamp result in the calucaltion, that's perfectly legal and something I might want to display rather than the number of seconds. (for example):
elapsed_time_c = endTime - startTime
- startTime = 8:00
- endTime = 10:00
- result (time) = 2:00:00
- result (number) = 7200
[ startTime = GetAsTime("8:00")
; endTime = GetAsTime("10:00")
; elapsed = endTime - startTime
; TS = GetAsTime(elapsed)
; NUM = GetAsNumber(elapsed)
; result = TS & Char(13) & NUM
Right....I think we're saying the same thing: don't average a single timestamp field by itself, but rather a elapsed (difference) of two timestamps.
1 of 1 people found this helpful
timestamp can't be added to timestamp, then can't calculate their average.
FM does auto convert to difference ("interval" in pgsql) or rather say it is already difference in internal value.