8 Replies Latest reply on Apr 30, 2017 2:21 PM by fmpdude

    exec sql problems

    PeterCortiel

      This does not work

       

      Executesql("

      Select d.enddate ,

      sum(amount) ,

      sum(intamount) from importdetail D

      join importheader H

          on D.kf_importheader_id=H.kp_importheader_id

      Group by d.enddate

      where d.agency = ?

      ";

      Char(9);

      "";

      "AES"

      )

      ... Returns ?

      This does:

      Executesql("

      Select

      sum(intamount) from importdetail D

      join importheader H

          on D.kf_importheader_id=H.kp_importheader_id

      where d.agency = ?

      ";

      Char(9);

      "";

      "AES"

      )

      Returns 1560.08

       

       

      Does filemaker not support group by or does it have  a special version of it

       

      How can I make the groupie work??

        • 1. Re: exec sql problems
          fmpdude

          Yes, FMP supports GROUP BY but only a field. You can't have a function with the GROUP BY also as with other environments.

           

          To help you debug your situation and avoid the maddening "?", why don't you just use a tool like RazorSQL that connects to your live FMP database so you'll get real error messages, you can just type SELECT syntax, you'll get query assist, etc. (you know, all the stuff that should be in FMP already...).

           

          HOPE THIS HELPS.

          1 of 1 people found this helpful
          • 2. Re: exec sql problems
            beverly

            You have:

            " Group by d.enddate

            where d.agency = ?"

             

            Try:

            WHERE D.agency = ?

            GROUP BY D.endate

             

            "GROUP BY" comes after the "WHERE"

             

            to debug:

            1. try EvaluationError() around the ExecuteSQL(). I will return a number that will be a clue to the problem. ( Syntax error? )

            2. or if you have FMPro Advanced, follow the instructions here:

            for more descriptive errors

            beverly

            • 3. Re: exec sql problems
              PeterCortiel

              I am group by a field!

              And razorsql looks interesting but way to confusing and it costs money i believe. I think FM should allow debugging all its components

              • 4. Re: exec sql problems
                okramis

                Executesql("

                Select d.enddate ,

                sum(amount) ,

                sum(intamount) from importdetail D

                join importheader H

                on D.kf_importheader_id=H.kp_importheader_id

                Group by d.enddate

                where d.agency = ?

                ";

                Char(9);

                "";

                "AES"

                )

                ... Returns ?

                What are you using the join for, I don't see using a field of it? And you're inconsequent in using the table alias, upper, lower, none. Then GROUP BY has to be after WHERE, else you have to use HAVING

                 

                SELECT d.enddate,

                SUM(d.amount),

                SUM(d.intamount)

                FROM importdetail d

                WHERE d.agency=?

                GROUP BY d.enddate

                 

                should work

                 

                otmar

                1 of 1 people found this helpful
                • 5. Re: exec sql problems
                  PeterCortiel

                  but. the endddate is in the header file

                   

                  Executesql("

                  Select h.enddate ,

                  Sum( d.amount) ,

                  sum(d.intamount)

                  from importdetail d

                  join importheader h

                      on d.kf_importheader_id=h.kp_importheader_id

                  where d.agency = ?

                  Group by  h.enddate

                  ";

                  Char(9);

                  "";

                  "AES"

                  )

                  This does not work

                  • 6. Re: exec sql problems
                    okramis

                    but. the endddate is in the header file

                     

                    Executesql("

                    Select h.enddate ,

                    Sum( d.amount) ,

                    sum(d.intamount)

                    from importdetail d

                    join importheader h

                    on d.kf_importheader_id=h.kp_importheader_id

                    where d.agency = ?

                    Group by h.enddate

                    ";

                    Char(9);

                    "";

                    "AES"

                    )

                    This does not work

                    Try this:

                     

                    Executesql("

                    Select h.enddate ,

                    Sum( d.amount) ,

                    sum(d.intamount)

                    from importdetail d

                    join importheader h

                    on d.kf_importheader_id=h.kp_importheader_id AND d.agency=?

                    Group by h.enddate

                    ";

                    Char(9);

                    "";

                    "AES"

                    )

                    1 of 1 people found this helpful
                    • 7. Re: exec sql problems
                      beverly

                      ExecuteSQL("

                           SELECT h.enddate ,

                                SUM(d.amount) ,

                                SUM(d.intamount)

                           FROM importheader h

                                JOIN importdetail d

                                     ON d.kf_importheader_id=h.kp_importheader_id

                                     AND d.agency=?

                           GROUP BY h.enddate

                      ";

                           Char(9);

                           "";

                           "AES"

                      )

                      • 8. Re: exec sql problems
                        fmpdude

                        If you have problems with Razor, there are several folks here on the forum that use it.

                         

                        You can evaluate it free for I believe 30 days. After that it's only $99 and will work with ANY JDBC-capable database, not just FMP.

                         

                        Actually, I think Razor is much easier than the "Data Viewer".