3 Replies Latest reply on Jun 15, 2015 7:04 AM by beverly

    How to use variable within Execute SQL SELECT statement cause WHERE id?

    Watchara

      I need to select Min(Column) FROM (Table) Where Id (?).

       

      I don't know how to set ID Variable.

       

      Can anyone show me right command ?

        • 1. Re: How to use variable within Execute SQL SELECT statement cause WHERE id?
          wimdecorte

          Do you mean "where id in (a,b,c,d)"

          or

          "where id = ?"

           

          if the latter the syntax is:

           

          ExecuteSQL( "select min(column) from table where id = ?" ; "" ; "" ; <value for id>)

          so no () around table.  And FM will insert the value for id for the "?" placeholder and make sure it is properly quoted.

           

          If you want to use the IN statement then you can't use a "?" placeholder and insert a parameter.  You have to provide the whole list and make sure it is correctly syntaxed and quoted.

          • 2. Re: How to use variable within Execute SQL SELECT statement cause WHERE id?
            Watchara

            Table Exam


            id     u_id     Number

            1        1        100

            1        2        500

            1        3        50

            1        4        150

            2        1        200

            2        2        180

            2        3        250

            2        4        560



            ExecuteSQL( "select min(Number) from Exam where id = ?" ; "" ; "" ; Exam::id)


            Is it correct?


            • 3. Re: How to use variable within Execute SQL SELECT statement cause WHERE id?
              beverly

              Yes! that is correct. id = 1 would result with 50, id = 2 would result with 180.

               

              beverly

               

              On Jun 15, 2015, at 9:58 AM, Watchara <noreply@filemaker.com> wrote

               

               

              How to use variable within Execute SQL SELECT statement cause WHERE id?

              reply from Watchara in Discussions - View the full discussion

              Table Exam

               

              id     u_id     Number

              1        1        100

              1        2        500

              1        3        50

              1        4        150

              2        1        200

              2        2        180

              2        3        250

              2        4        560

               

               

              ExecuteSQL( "select min(Number) from Exam where id = ?" ; "" ; "" ; Exam::id)

               

              Is it correct?