3 Replies Latest reply on Jan 17, 2013 6:36 AM by beverly

    Execute sql "or"

    Hudi

      Hey All,

       

      I'm tryting to use the "or" clause inside an execute sql function and am returning "?"/

       

      ExecuteSQL (

       

       

      "SELECT Count("_K_ID_Student")

      FROM "Student"

        WHERE "class_id" = ? and ("GPA" >? or "Active" ≠ ?")

       

       

       

      ; "" ; "" ; Class::K_ID; ;3.0; "1" )

       

      This is just a sample but if you wanted to get a count of all the students who are in a class who have a gpa over 3.0 or are incative. This example is really sorry, but I hope my point is clear. How do you place an "and" clause and an "or" in the same sql function.

       

       

      Cheers

        • 1. Re: Execute sql "or"
          beverly

          your WHERE statement looks good. your parameters do not. You have 3 ? and 4 places defined.

           

               ; "" ; "" ; Class:K_ID ; 3.0 ; 1 )

           

           

              ;    "" ;    "" ;       Class::K_ID; ;3.0;   "1" )

           

          Beverly

          • 2. Re: Execute sql "or"
            Hudi

            You are correct.

             

            ExecuteSQL (

             

             

                                                    "SELECT Count(\"_K_ID_Student\")

                                                    FROM \"Student\"

                                                     WHERE \"class_id\" = ? and  (\"GPA\" >?  or  \"Gifted\"  = ?")

             

            ;    "" ;    "" ;

                  Class::K_ID ; 3.0 ;   "1" )

             

             

             

                                                                                                         

            So, we are trying to find a count of all the students in the class who are either gifted or have a high GPA.

             

            I hope this makes it a bit clearer.

             

            I can't seem to get this to work in FM with actual tables. What I'm trying to understand is how to structure an "Or" statement. Does it apply to the entire argument or just to what is inside the ( ). I need it to be isolated to just whats inside the  ( ).

             

            Thanks again

            • 3. Re: Execute sql "or"
              beverly

              OK! you are using Aggregates, so may need the GROUP BY clause and/or the HAVING clause. I don't know the structure of your tables, so can't verify what query to use.

               

              Beverly