10 Replies Latest reply on Sep 26, 2014 2:00 AM by user19752

    How to get Row Number with executeSQL

    Paul Jansen

      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:

       

      1 fred

      2 bert

      3 mary

      4 alice

       

      Am I missing something obvious? (I certainly hope so!)

       

      Thaks

       

      PJ

        • 1. Re: How to get Row Number with executeSQL
          beverly

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

          Beverly Voth

          --

          • 2. Re: How to get Row Number with executeSQL
            Paul Jansen

            I did try that,

             

            SELECT  ROW_Number() , first_name

            FROM contacts

            etc


            It didn't work.  Remove ROW_Number() , and a valid result is returned

             

             

            • 3. Re: How to get Row Number with executeSQL
              beverly

              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.

              1 of 1 people found this helpful
              • 4. Re: How to get Row Number with executeSQL
                user19752

                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

                AND t1.num1>=t2.num1

                GROUP BY t1.text1

                ORDER BY 1

                " ; "" ; "" )

                 

                The sort order is defined in WHERE phrase.

                WHERE t1.num1<0.1 AND t2.num1<0.1

                means search criteria usual is there,

                AND t1.num1>=t2.num1

                means counting row number (ascending num1).

                (so ordering column "num1" should be unique)

                 

                Using Filemaker custom function may be best for this.

                • 5. Re: How to get Row Number with executeSQL
                  beverly

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

                   

                   

                  Beverly

                  • 6. Re: How to get Row Number with executeSQL
                    DamianKelly

                    I would very much doubt the ORDER would happen before the SELECT. I know it doesnt in Transact or MySQL.

                    • 7. Re: How to get Row Number with executeSQL
                      beverly

                      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.

                       

                      Beverly

                      • 8. Re: How to get Row Number with executeSQL
                        DamianKelly

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

                        • 9. Re: How to get Row Number with executeSQL
                          Paul Jansen

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

                           

                          Cheers

                           

                          PJ

                          • 10. Re: How to get Row Number with executeSQL
                            user19752

                            Now I got it.

                            I tested on table

                            text1 (text)

                            num1 (number)

                            rownum (unstored calc get(rownumber))

                             

                            SELECT recnum, text1

                            FROM SQLrownum

                            ORDER BY num1

                             

                            This seems ORDERed AFTER calced recnum.

                            Adding num1 to  SELECT

                             

                            SELECT recnum, text1, num1

                            FROM SQLrownum

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