I am running an executeSQL call with a couple of inner joins which returns a number of rows. I want each row to start with the row numberlike this:
Am I missing something obvious? (I certainly hope so!)
ROW_NUMBER() is a SQL function. I haven't tested with FMP eSQL. Put it as a "column" in the SELECT. If you get '?' It probably is not valid. To be sure, limit the columns in your test query.
-- sent from my iPhone4 --
I did try that,
SELECT ROW_Number() , first_name
It didn't work. Remove ROW_Number() , and a valid result is returned
Yes, I just tested it. Not valid with eSQL!
The other thought is to have a field in the database (calculated, unstored) with the Get ( recordNumber) and query that column.
Yes, that seems to work ok for me even with or without ORDER BY ( sort ). I can use the default of ASC or DESC for the sort and it works well.
Hi, I think this is not able with unstored field, since the column value is calculated BEFORE
sort since the value may need to sort.
(tested on FM12/13 on Windows7)
On special case only , done by these way
ExecuteSQL ( "
SELECT COUNT(t2.num1), t1.text1
FROM SQL_rownum t1, SQL_rownum t2
WHERE t1.num1<0.1 AND t2.num1<0.1
GROUP BY t1.text1
ORDER BY 1
" ; "" ; "" )
The sort order is defined in WHERE phrase.
means search criteria usual is there,
means counting row number (ascending num1).
(so ordering column "num1" should be unique)
Using Filemaker custom function may be best for this.
OK, it works as expected for me Mac FM12 (sorry didn't test further) to use the unstored calc field. Otherwise what would be the point of making a query to FM with other unstored calc fields using eSQL? I'm sure that's necessary (sometimes). I guess we don't have an "under the hood" to know whether the sort (ORDER BY) is performed before the find (SELECT, WHERE) when using eSQL.
With CWP, there is the -script, -script.prefind & -script.presort. These allow us to designate WHEN certain things happen. The default is to run a script after any find and any sort in a "query" (request). I guess I just thought perhaps the same was happening with eSQL ( the sort was before the find ). Hmmmmm
And using the unstored calc was one way. Perhaps using the text in the result could post-process the rownum before each "line" (if the pilcrow "¶" is the record/row separator is used the value functions would help).
I would very much doubt the ORDER would happen before the SELECT. I know it doesnt in Transact or MySQL.
It's FileMaker and it's ExecuteSQL. Unless someone at FMI is willing to give us the guts of the function, I'm just guessing. And I tested so much that compared to Transact (MS SQL) and MySQL. Some worked, some didn't, - I'm taking it all with a grain of salt.
Shame there is no LIMIT in FileMaker, with that it would be trivial to prove one way or the other. I am racking my brains on something else as a proof.....
The unstored calc does seem to work as I would hope. I get ascending row numbers regardless of the ORDER BY and WHERE clauses. Works on both Mac and PC
It seems that the unstored calc is resolved aftert the sort in a similar way to using an unstored get(foundCount) in a child table correctly displays the number of matching records through a relationship.
All very handy to know and quite useful
Thanks to all for the input. I have my answer, however I am not going to use it in this case as the requirement was part of an attempt to optimize a process by smart use of executesql to avoid having to loop through the returned data afterwards - in the end it is no faster and more complex. I think I will try cascading IDs down the relationship chain to avoid the need for two inner join clauses and see if that improves performance. Any thoughts on whetehr this is worth doing would be much appreciated...
Now I got it.
I tested on table
rownum (unstored calc get(rownumber))
SELECT recnum, text1
ORDER BY num1
This seems ORDERed AFTER calced recnum.
Adding num1 to SELECT
SELECT recnum, text1, num1
This seems ORDERed BEFORE calc recnum.
Working case is Very useful !
But when the SQL is more complex and get(x) is other flags, it may be difficult to see whether it is working or not ?
We need more documentation.
add comment by user19752 on 2014-09-26:
Here I made too many typoes in row and rec(ord)...
Retrieving data ...