1 2 3 4 Previous Next 55 Replies Latest reply on Sep 18, 2015 9:56 AM by bertrand

    Execute SQL in Filemaker

    jurijn

      Hey developers,

       

      I used some SQL to get some data back from FM.

      I have problems with the returned data, that means numbers contains decimal places. I dont want them so I wanted to use round (column, 0) function in a query (without success).

       

      That is the query, that I used in executeSQL function inside a set variable script step:

      ..."SELECT worker_name, round( sum( revenue ), 0 ) FROM sales where worker_name IS NOT NULL group by worker_name"...

       

      I could´t manage function to work. If I run executeSQL without round function in a query everything works fine.

       

      Is that not supported in FM or is the query wrong? I am not quite sure that must I admit

       

      Please share some your opinion and experience on that topic!

      Thanks!

        • 1. Re: Execute SQL in Filemaker
          user19752

          No calculation on result of aggregate functions are supported.

          bad: sum(n) + 1

          good: sum(n+1)

          • 2. Re: Execute SQL in Filemaker
            erolst

            user19752 wrote:

             

            No calculation on result of aggregate functions are supported.

            bad: sum(n) + 1

            good: sum(n+1)

             

            But that is not the same – just like

             

            Round ( Sum ( revenue ) , 0 )

             

            isn't the same as

             

            Sum ( Round ( revenue, 0 ) )

            (which works in ExecuteSQL() )

             

            btw., Round ( someField , 0 ) = Int ( someField ).

            • 3. Re: Execute SQL in Filemaker
              okramis

              user19752 schrieb:

               

              No calculation on result of aggregate functions are supported.

              bad: sum(n) + 1

              good: sum(n+1)

              Unfortunately! My workaround is, do a first ExecuteSQL with the aggregate, populate a VirtualList with the result and do a 2nd ExecuteSQL on the VirtualList doing the calculations. I do this all in single let-statement, as the VL resides in RAM this is almost instant, is sort of a temp-table.

              The whole thing because ROUND(SUM(x)) is not the same as SUM(ROUND(x))...

              • 4. Re: Execute SQL in Filemaker
                erolst

                okramis wrote:

                a first ExecuteSQL with the aggregate, populate a VirtualList with the result and do a 2nd ExecuteSQL on the VirtualList doing the calculations. I do this all in single let-statement, as the VL resides in RAM

                If the VL resides in memory, what do you use as table/field references in that second query?

                • 5. Re: Execute SQL in Filemaker
                  okramis

                  If the VL resides in memory, what do you use as table/field references in that second query?

                  Hi Erolst

                  It's of corse a "real" VL, but as the fields of the VL only sort of parse the $$variable, I think it's all in memory. It needs to take care of the data-types in the 2nd query for calculations though, as for flexibility the VL-fields may be text-fields. I.E. SELECT Field01, ROUND(NUMVAL(Field02)) FROM VL WHERE ID IS NOT NULL

                   

                  Regards

                  Otmar

                  • 6. Re: Execute SQL in Filemaker
                    rrrichie

                    You could try this...

                     

                    SELECT INT ( (SELECT ( SUM ( S.Revenue ) ) FROM Sales S WHERE S.name=N.name) ) as rev_int, name FROM Sales N  GROUP BY N.name

                     

                    Always wondered why that one does work...

                     

                    Think it's because the INT works on a number in that case and not on an aggregate function :-)

                    • 7. Re: Execute SQL in Filemaker
                      rrrichie

                      Actually that is a nifty solution,  I see all sorts of solutions for other queries :-)

                      • 8. Re: Execute SQL in Filemaker
                        okramis

                        must be "SELECT Field01, ROUND(NUMVAL(Field02)) FROM VL WHERE Field01 IS NOT NULL" or any field, that must have a value

                        • 9. Re: Execute SQL in Filemaker
                          okramis

                          rrrichie schrieb:

                           

                          You could try this...

                           

                          SELECT INT ( (SELECT ( SUM ( S.Revenue ) ) FROM Sales S WHERE S.name=N.name) ) as rev_int, name FROM Sales N  GROUP BY N.name

                           

                          Always wondered why that one does work...

                           

                          Think it's because the INT works on a number in that case and not on an aggregate function :-)

                          Here you do a subquery which returns a single value, so you're not doing a calculation on a aggregate, a good workaround too!

                          P.S. as ExecuteSQL does not return any column names it's not needed to specify the "as rev_int"

                          • 10. Re: Execute SQL in Filemaker
                            erolst

                            rrrichie wrote:

                            Always wondered why that one does work...

                             

                            Think it's because the INT works on a number in that case and not on an aggregate function :-)

                             

                            There's no problem in IT that cannot be solved by adding more levels of indirection …

                            • 11. Re: Execute SQL in Filemaker
                              rrrichie

                              yeah I know, never lost the habit :-)

                              would be great if FM gets a little tiny very small update up from SQL 92...

                              But that will probably never happen...

                              Running aggregating scripts at nite does give a whole load of new possibilities :-).

                               

                              Like in the above example, if the Sales table gets really big, I would just aggregate it at nite.  And if the user wants real time data you only have to add the sales of today.

                              • 12. Re: Execute SQL in Filemaker
                                rrrichie

                                Yes that is directly correct

                                • 13. Re: Execute SQL in Filemaker
                                  beverly

                                  Can you expound on this (more detail please!) how do you have a virtual list (table) in RAM?

                                   

                                  beverly

                                  On Aug 7, 2015, at 5:46 AM, okramis:

                                   

                                  as the VL resides in RAM this is almost instant, is sort of a temp-table.

                                   

                                  • 14. Re: Execute SQL in Filemaker
                                    rrrichie

                                    If you use the virtual_rows/list  technique where each row in a table fetch the values from a global then since globals are stored per user on his own machine, one can assume it would be in RAM or Cache if it ain't too big.

                                     

                                    FileMaker Virtual List Technique

                                    1 2 3 4 Previous Next