1 2 Previous Next 19 Replies Latest reply on Jun 8, 2014 8:55 PM by beverly

    FQL Question

    Hudi

      Hi,

       

      I need some help writing an sql statement to find out how many times an album was quoted during the current week. Two tables ALBM and QLI ( quote lineItem) that need to be joined.

       

      What I'd like to see is this:

       

      sample album - 2

      sample album xyz - 4

      another album - 1

      a different album - 7

      etc.

       

       

       

      ExecuteSQL (

       

      “SELECT CDTitle, COUNT (QLI.id)

       

      FROM ALBM

       

      WHERE QLI.date_created ≤ ? and QLI.date_created ≥ ?

       

      LEFT JOIN QLI on id_album = QLI.id

       

      GROUP BY QLI.id

       

      "

      ; "" ; "" ; $_weekstart ; $_weekend )

       

       

      Thanks a ton!

        • 1. Re: FQL Question
          beverly

          GROUP BY CDTitle

           

          You don't include the COUNTed field in the GROUP BY clause, just the other field.

           

           

          -- sent from my iPhone4 --

          Beverly Voth

          --

          • 2. Re: FQL Question
            beverly

            Sorry, Hudi, the type is so small I missed a bunch.

             

                 See this reference for assistance with ExecuteSQL():

            <http://filemakerhacks.com/2012/11/02/pdf-version-of-the-missing-fm-12-executesql-reference>

             

            Your complete query should be like this:

             

                 SELECT CDtitle, Count( QLI.id ) AS count_qli_id

             

                 FROM ALBM

                      LEFT JOIN QLI ON id_album = QLI.id

             

                 WHERE QLI.date_created  ≤ ?

                      AND QLI.date_created  ≥ ?

             

                 GROUP BY CDtitle

             

            Your first clause is the SELECT (listing fields and functions). Use alias for the field "name" if using functions.

            Your second clause is the FROM and JOIN(s) listing the tables and whatever makes the match.

            Your third clause is the WHERE listing any find or match criteria not as part of the joins.

            Your fourth clause is the GROUP BY and necessary to list any fields not summarized in the SELECT.

            If you wish to sort by any particular fields or the result of the summary, then add ORDER BY as the last clause.

             

            Also, the key word BETWEEN may (or may not) work instead of the two statements in the WHERE clause

             

                 WHERE QLI.date_created BETWEEN ? AND ?

             

             

             

            Beverly

            1 of 1 people found this helpful
            • 3. Re: FQL Question
              Mike_Mitchell

              +1 on BETWEEN.

              • 4. Re: FQL Question
                user19752

                I like BETWEEN but on FM ExecuteSQL() it is very slower than <= .

                • 5. Re: FQL Question
                  beverly

                  I've seen the same happen in SQL dbs, too. BETWEEN works, but at a price...

                   

                  Beverly

                  • 6. Re: FQL Question
                    Mike_Mitchell

                    Interesting. I must be doing something wrong, because I've had trouble getting the ranged date queries to work properly using <= / >=. Is there some trick to it?

                    • 7. Re: FQL Question
                      beverly

                      How are you dates (yyyy-mm-dd, ???)

                       

                      Post your query, so we can critique.

                       

                      -- sent from my iPhone4 --

                      Beverly Voth

                      --

                      • 8. Re: FQL Question
                        Mike_Mitchell

                        Well, now I’m scratching my head … and feel pretty stupid.   

                         

                        It’s been a couple of years since I played with this, so my memory is a bit fuzzy. Here’s a sample of what is in the production system:

                         

                        Let ( [

                        date01 = "1/1/2012" ;

                        date02 = "12/31/2012" ;

                        eventStatusID01 = "6347304944400028" ;

                        sqlQuery = "SELECT event.eventID FROM event WHERE event.eventDate BETWEEN '" & date01 & "' AND '" & date02 & "' AND event.eventStatusID = '" & eventStatusID01 & "'"

                        ] ;

                         

                        ExecuteSQL ( sqlQuery ; "|" ; "¶" )

                         

                        )

                         

                        Of course, this is what I have in the Data Viewer; the real system passes in the parameters as variables.

                         

                        Then, for comparison, I tried this:

                         

                        Let ( [

                        date01 = "1/1/2012" ;

                        date02 = "12/31/2012" ;

                        eventStatusID01 = "6347304944400028" ;

                        sqlQuery = "SELECT event.eventID FROM event WHERE event.eventDate >= '" & date01 & "' AND event.eventDate <= '" & date02 & "' AND event.eventStatusID = '" & eventStatusID01 & "'"

                        ] ;

                         

                        ExecuteSQL ( sqlQuery ; "|" ; "¶" )

                         

                        )

                         

                        and I get the exact same result.

                         

                        If my memory serves correctly, I think I did try the Unix date format (YYYY-MM-DD), and just passing in the FileMaker date, before resorting to BETWEEN. But what may have happened is I forgot to enclose the date in single quotes. (When I take out the single quotes, I get the dreaded “?”.) So maybe that was the difficulty.

                         

                        Sorry for wasted bandwidth. (Sheepishly slinking away … )   

                         

                        Mike

                        • 9. Re: FQL Question
                          beverly

                          Yep! the single quote is necessary for dates.

                           

                          You don't need to format a date if you pass as a parameter.

                          • 10. Re: FQL Question
                            Hudi

                            Thank you for the insights. I've gotten it working over the weekend with this:

                             

                            ExecuteSQL (

                             

                             

                            "SELECT a.\"CDTitle\", COUNT ( b.\"id_album\") as quoteCount

                            FROM \"QLI\" b

                            Left JOIN \"ALBM\" a ON b.\"id_album\" = a.\"id\"

                            WHERE b.\"date_created\" >= ?

                            GROUP BY a.\"CDTitle\"

                            "

                            ; "" ; ""  ; Get(CurrentDate) - 7)

                             

                            Beverly, I like the between function and have used it in other queries (I've gotten a little trigger happy with the executeSQL lately )

                             

                            Thanks,

                             

                            Hudi

                            • 11. Re: FQL Question
                              Hudi

                              I have another one while I'm on the subject. This is supposed to genarate a list of id's based on what the user types into the Global::track__et field ( and then filter a portal).

                               

                              ExecuteSQL ( "

                               

                                        SELECT id FROM TRK

                                        WHERE \"TrackTitle\" LIKE ?

                              "

                              ; "" ; ""  ;  Global::track__et &"%"

                              )

                               

                              I suspect it has to do with quotes somewhere....

                               

                              Thanks!!

                              • 12. Re: FQL Question
                                erolst

                                Hudi wrote:

                                I suspect it has to do with quotes somewhere....

                                Single quotes, actually. Maybe better visible (note the bolded little thingies!) if you do it like:

                                 

                                Let (

                                ~sql = "

                                  SELECT id FROM TRK

                                  WHERE \"TrackTitle\" LIKE '" & Global::track__et & "%'"

                                  ;

                                 

                                  ExecuteSQL ( ~sql ; "" ; "" )

                                )

                                • 13. Re: FQL Question
                                  beverly

                                  id may be reserved word so "\id"\ is needed.

                                   

                                  -- sent from my iPhone4 --

                                  Beverly Voth

                                  --

                                  • 14. Re: FQL Question
                                    Hudi

                                    Thanks for the suggestion. I tried this ( below )  and many other variations with no luck. Either getting a "?" or no results. Any ideas? 

                                     

                                    ExecuteSQL (

                                     

                                     

                                    "SELECT \"id\"

                                    FROM \"TRK\"

                                    WHERE \"TrackTitle\" LIKE ?" ;

                                     

                                     

                                    "";"";    " ' " &Global::track__et  & "%' "

                                     

                                     

                                    )

                                     

                                    Thanks!

                                    1 2 Previous Next