1 2 3 Previous Next 34 Replies Latest reply on Nov 22, 2015 3:10 AM by okramis

    ExecuteSQL?????

    weedonpaul

      What is wrong with the following:

       

      ExecuteSQL("SELECT publication_month FROM Coverage WHERE _kf_clients_id = ? AND publication_date >=? AND publication_date <=? GROUP BY publication_month";"";""; Coverage_Report::_kf_clients_id; Coverage_Report::start_date; Coverage_Report::end_date)

       

      ExecuteSQL("SELECT SUM(count) FROM Coverage WHERE _kf_clients_id = ? AND publication_date >=? AND publication_date <=? GROUP BY publication_month";"";""; Coverage_Report::_kf_clients_id; Coverage_Report::start_date; Coverage_Report::end_date)

       

      both of these send back a ?

       

      the top one is to get x-axis tables in a bar graph

       

      the bottom is to sum up a field called count that has the number 1 in it (there is a better way I know but I don't think thats the problem) to count the frequency of coverage in a month.

       

      I should add that I pinched this code from an example that was kindly sent to me to solve another problem and I (hesitate to the use the word) tweaked it.

        • 1. Re: ExecuteSQL?????
          Mike_Mitchell

          You can't use leading underscores in table or field names in SQL. Your _kf ... syntax is breaking the query. You have two choices:

           

          1) Use the Quote ( ) function or insert escaped quotes to tell the parser these are literals.

           

          2) Change your field names to something compliant.

           

          https://fmhelp.filemaker.com/docs/14/en/fm14_sql_reference.pdf

          • 2. Re: ExecuteSQL?????
            okramis

            What is wrong with the following:

             

            ExecuteSQL("SELECT publication_month FROM Coverage WHERE _kf_clients_id = ? AND publication_date >=? AND publication_date <=? GROUP BY publication_month";"";""; Coverage_Report::_kf_clients_id; Coverage_Report::start_date; Coverage_Report::end_date)

            You need to quote your fields starting with underline "_" like this: .....\"_kf_clients_id\" = ?....

            and you're not doing any aggregation, so why the GROUP BY, did you mean ORDER BY?

            ExecuteSQL("SELECT SUM(count) FROM Coverage WHERE _kf_clients_id = ? AND publication_date >=? AND publication_date <=? GROUP BY publication_month";"";""; Coverage_Report::_kf_clients_id; Coverage_Report::start_date; Coverage_Report::end_date)

            Same here, _kf_clients_id needs to be quoted. Here I'd add the publication_month to the SELECT-statement, else you won't know to which months the results belong.

             

            regards

            Otmar

             

            P.S. just saw, Mike was posting while I was still writing;-)

            • 3. Re: ExecuteSQL?????
              Mike_Mitchell

              Yeah, hate that ...  

              • 4. Re: ExecuteSQL?????
                weedonpaul

                ExecuteSQL("SELECT (publication_month) FROM (Coverage) WHERE (_kf_clients_id )= ? AND (publication_date) >=? AND (publication_date) <=? GROUP BY (publication_month)";"";""; (Coverage_Report::_kf_clients_id); (Coverage_Report::start_date); (Coverage_Report::end_date))

                 

                ExecuteSQL("SELECT SUM(count) FROM (Coverage) WHERE (_kf_clients_id) = ? AND (publication_date) >=? AND (publication_date) <=? GROUP BY (publication_month)";"";""; (Coverage_Report::_kf_clients_id); (Coverage_Report::start_date); (Coverage_Report::end_date))

                 

                It still didn't work I have put every reference to a field in ()

                thank you for the resource i'm reading it now

                • 5. Re: ExecuteSQL?????
                  Mike_Mitchell

                  No, you have to enclose the field name in QUOTES.

                   

                  “SELECT publication_month FROM Coverage WHERE \”_kf_clients_id\” = ? … “

                   

                  and so forth.

                  • 6. Re: ExecuteSQL?????
                    weedonpaul

                    Sorry I replied before seeing the added replies I'll have another play and get back

                    • 7. Re: ExecuteSQL?????
                      weedonpaul

                      ExecuteSQL(

                       

                      "SELECT publication_month FROM Coverage WHERE \"_kf_clients_id \"= ? AND publication_date >=? AND publication_date <=? ORDER BY (publication_month)"

                      ;"";""; Coverage_Report::_kf_clients_id; Coverage_Report::start_date; Coverage_Report::end_date)

                       

                      Are my less than and more than signs are right? It's still not working

                       

                      ExecuteSQL(

                       

                      "SELECT COUNTpublication_month FROM Coverage_For_Report WHERE \"_kf_clients_id\" = ? AND publication_date >=? AND publication_date <=? GROUP BY publication_month"

                      ;"";""; Coverage_Report::_kf_clients_id; Coverage_Report::start_date; Coverage_Report::end_date)

                      • 8. Re: ExecuteSQL?????
                        Mike_Mitchell

                        publication_date >=? AND publication_date <=?


                        This will only work if the publication_date is exactly equal to the start_date and the end_date. Is that what you wanted?

                        • 9. Re: ExecuteSQL?????
                          weedonpaul

                          I want publication date MoreThan or Equal to start date AND LessThan or Equal to end date

                           

                          I have started to break it down to find out whats not working, see below. It worked fine until I add the underlined stuff

                           

                          ExecuteSQL (

                           

                           

                          "SELECT count (*)

                          FROM coverage_For_Report

                          WHERE\"_kf_clients_id\"= ?"

                           

                           

                          ;"";"";Coverage_Report::_kf_clients_id)

                          • 10. Re: ExecuteSQL?????
                            weedonpaul

                            Actually solved that one as I forgot space after WHERE

                            • 11. Re: ExecuteSQL?????
                              Mike_Mitchell

                              The table names don’t match. Is it Coverage_Report, or Coverage_For_Report?

                               

                              This is why it’s a good idea to include the table names in your queries.

                              • 12. Re: ExecuteSQL?????
                                beverly

                                other advise on quoting the names of tables and/or field, good.

                                ExecuteSQL("SELECT publication_month FROM Coverage WHERE _kf_clients_id = ? AND publication_date >=? AND publication_date <=? GROUP BY publication_month";"";""; Coverage_Report::_kf_clients_id; Coverage_Report::start_date; Coverage_Report::end_date

                                what's wrong:

                                     1. GROUP BY and no aggregate in the SELECT

                                     2. underscore as first character in names (use quotes, as others have stated)

                                 

                                ExecuteSQL("SELECT SUM(count) FROM Coverage WHERE _kf_clients_id = ? AND publication_date >=? AND publication_date <=? GROUP BY publication_month";"";""; Coverage_Report::_kf_clients_id; Coverage_Report::start_date; Coverage_Report::end_date)

                                 

                                what's wrong (in addition to underscore starts names):

                                     1. the field/column name 'count' is RESERVED WORD and must also be quoted:

                                          SUM(\"count\")

                                     2. you use the GROUP BY (because you have the aggregate SUM). However, you must list the field/column in your GROUP BY in your SELECT as well:

                                 

                                SELECT someField, SUM(anotherField)

                                FROM someTable

                                GROUP BY someField

                                 

                                so... the use of COUNT(*) is very bad, for example, when trying to GROUP BY, because every field in your table must be listed!!

                                 

                                beverly

                                • 13. Re: ExecuteSQL?????
                                  weedonpaul

                                  I've got in a mess with table names

                                   

                                  the table that the layout is based on is Coverage_Report

                                   

                                  and its getting data from Coverage_For_Reports which is an occurrence of Coverage

                                  I don't suppose I need that occurrence now I'm using (or should I say Trying to use)SQL

                                   

                                  My test script works but only gives one value I thought the GROUP BY bit would list values for each group

                                  I checked the data and there should be more than one group, also the value is consistent with it ignoring the (AND publication_date >= ? AND publication_date <= ?) bit which worked before I added GROUP BY?

                                   

                                  Thank you so much for your time

                                   

                                  ExecuteSQL (

                                   

                                   

                                  "SELECT count (*)

                                  FROM coverage_For_Report

                                  WHERE \"_kf_clients_id\"= ? AND publication_date >= ? AND publication_date <= ?

                                  GROUP BY publication_month"

                                   

                                   

                                  ;"";"";Coverage_Report::_kf_clients_id; Coverage_Report::start_date; Coverage_Report::end_date)

                                  • 14. Re: ExecuteSQL?????
                                    Mike_Mitchell

                                    No, you don’t need the additional TO in the query.

                                     

                                    Mind Beverly’s advice. She is wise.

                                    1 2 3 Previous Next