7 Replies Latest reply on Sep 7, 2015 10:45 AM by PSI

    I am trying to use the IN keyword.

    PSI

      I am trying to use the IN keyword.

       

        ExecuteSQL (

        "SELECT Summary

        FROM Events

        WHERE \"Date\"=? AND Status IN (?) AND Location=?"; ""; "¶"; zG_DateCal[7] ; zL_FilterByStatus ; "Mainspace" )

       

      zL_FilterByStatus =

        Case ( IsEmpty ( zG_FilterByStatus ) or zG_FilterByStatus = "All" ;

        "'Confirmed'" & ", " & 'Hold'" & ", " & 'Cancelled'" ; zG_FilterByStatus )

       

      If zL_FilterByStatus = 'Confirmed', 'Hold', 'Cancelled' I get nothing.

      If zL_FilterByStatus = any individual work, without single quotes, it works.

       

      any ideas?

      thanks

      John

        • 1. Re: I am trying to use the IN keyword.
          coherentkris

          From FileMaker hacks...

          The Missing FM 12 ExecuteSQL Reference | FileMakerHacks

           

          WHERE … IN ( { SELECT … } )

          The IN keyword used for the WHERE clause, takes a comma-delimited list and searches by each of the values (an OR search). If the list is composed on numbers, the list is just comma-delimited. If the list is TEXT, then the values, must be enclosed with single quotes (‘abc’,’def’,’ghi’,’jkl’). These values will be automatically be quoted as needed, if you nest another SELECT inside. Only one column/field should be returned in the nested SELECT results.

           

          It is likely that the value in zl_FilterByStatus does not produce what the IN() clause requires.

           

          FM 12 ExecuteSQL: Dynamic Parameters, pt 2 | FileMakerHacks

           

          If your argument list is correct then i would suspect the IN() clause does not handle the ?.

           

          I would replace the argument with a sub query  IN ( SELECT status_field FROM wherever_is_exists )

          • 2. Re: I am trying to use the IN keyword.
            Mike_Mitchell

            "If your argument list is correct then i would suspect the IN() clause does not handle the ?."

             

            Confirmed. I just tested with a simple query, and explicitly calling out the list works, but the parameterized version doesn't.

             

            You can probably do this:

             

            ExecuteSQL (

              "SELECT Summary

              FROM Events

              WHERE \"Date\"=? AND Status IN (" & zL_FilterByStatus & ") AND Location=?"; ""; "¶"; zG_DateCal[7] ; "Mainspace" )

            • 3. Re: I am trying to use the IN keyword.
              erolst

              You cannot use ? to inject syntactical constructs, only values.

               

              Try

               

              Let ( [

                 ~f = zG_FilterByStatus ;

                 array = Case (

                   IsEmpty ( ~f ) or ~f = "All" ;

                   "Confirmed¶Hold¶Cancelled" ;

                  ~f

                )

              ] ;

                ExecuteSQL ( "

                  SELECT Summary

                  FROM Events

                  WHERE

                    \"Date\" = ? AND

                    Status IN ('" & Substitute ( array ; ¶ ; "','" & "') AND

                    Location = ?

                  " ; "" ; "" ; zG_DateCal[7] ; "Mainspace"

              )


              Means also you can do without the other calculation field.

              • 4. Re: I am trying to use the IN keyword.
                PSI

                Mike,

                 

                Thanks! That worked!

                 

                I thought I tried that before posting?

                 

                I will mark it correct as soon as I can get back into my account.

                 

                John Morina

                Pueblo Systems, Inc.

                O: 631.549.0969

                C: 516.443.0966

                john.morina@pueblo-systems.com

                 

                • 5. Re: I am trying to use the IN keyword.
                  PSI

                  I marked Mikes answer as correct because it works and I was able to implement it easily.

                   

                  As it turns out this doesn't work for filtering I really need but I have run into this before and now have a solution!!

                   

                  Thanks all

                  John

                  • 6. Re: I am trying to use the IN keyword.
                    wimdecorte

                     

                    I am trying to use the IN keyword.

                     

                      ExecuteSQL (

                      "SELECT Summary

                      FROM Events

                      WHERE \"Date\"=? AND Status IN (?) AND Location=?"; ""; "¶"; zG_DateCal[7] ; zL_FilterByStatus ; "Mainspace" )

                     

                     

                    If you want to use the "?" placeholder for an IN clause you have to specify as many ?s as there are values in the IN list:

                     

                    ExectueSQL ( ....<rest of the query> ... AND Status IN (?,?,?) ; value1 ; value2 ; value3 )

                     

                    Would work.

                    Obviously most of the time you don't know how many values you'll need.  But you can generate the whole SQL query syntax dynamically and use Evaluate.  I have a demo file of that floating around somewhere.

                    • 7. Re: I am trying to use the IN keyword.
                      PSI

                      Wim,

                       

                      I did see that as option but with a variable list it add a bit of complexity. I could get around it but  Status IN (" & zL_FilterByStatus & ") did work. Then I realized that although it worked I wasn't getting the result I needed.

                       

                      The field is actually 4 SQL statements, one for Confirmed events in the main space, confirmed events in the gallery and 2 more to represent Holds.

                       

                      I have to filter by Location and Status. I ended up with a case statement

                       

                      To show or not show Confirmed - main space I did this...

                      WHERE \"Date\"=? AND Location=? AND zF_Status=?" ; ""; "¶"; zG_DateCal[ 7 ] ;

                          Case ( zG_FilterByLocation = "All" or IsEmpty ( zG_FilterByLocation ) or zG_FilterByLocation = "Mainspace" ; "Mainspace" ) ;

                          Case ( zG_FilterByStatus = "All" or IsEmpty ( zG_FilterByStatus ) or zG_FilterByStatus = "Confirmed" ; 1 ))