5 Replies Latest reply on Nov 12, 2015 10:07 PM by RobertWard

    ExecuteSQL

    RobertWard

      I hope that someone can help me on this one.

       

      I have a Program table which contains programs names and two number fields representing the last four digits of the year. The two number fields are parsed out from a global field, which contains a date range e.g. 2014-2015. One number field is YEARFROM and the other number field is YEARTO. In order to get the YEARFROM and YEARTO, I parse them out into global variables as $$_YEARFROM and $$_YEARTO, so that I ca use them in a find operation.

       

      I am trying to write an ExecuteSQL statement which tells me if a particular program name exists within a date range like 2015 to 2016. Now I know that in the Program table I have two records that contain the program name "Secure Future$ Outreach" with a date of 2015 to 2016. Here is the SQL statement.

       

      ExecuteSQL ("

      SELECT Count(Name_Program)

      FROM PROGRAM

      WHERE Name_Program = 'Secure Future$ Outreach' and

      ' " & $$_YEARFROM & " ' = '2015' and

      ' " & $$_YEARTO & " ' = '2016' " ; "" ; "" )

       

      The result I get is 0 when the result should return 2. Notice that I am including the two variables in the WHERE statement. I do not get a ?, so this is telling me that the syntax should be correct or at least it's acceptable. For now I am just trying to get the results based on hard coded dates, ultimately, I will change this to use a dynamic result. One thing to note is that the variable name $$_YEARFROM is a reserved word in SQL. But I don't know if that applies to variable names. In any case, I did try to double quote the variable name as $$_"YEARFROM" and I get an error doing that.

       

      Any ideas from anyone on why I don't get my result of 2 returned?

        • 1. Re: ExecuteSQL
          beverly

          Likely the format is not quite right. And you cannot search IN the variables.

           

          When calling variables, I use the ? parameters:

           

          ExecuteSQL ("

          SELECT Count(Name_Program)

          FROM PROGRAM

          WHERE Name_Program = 'Secure Future$ Outreach' and

          YEARFROM = ? and

          YEARTO ? " ; "" ; ""

          ; $$YEARFROM; $$YEARTO )

           

          Does that help?

          beverly

          • 2. Re: ExecuteSQL
            RobertWard

            Thank you for your rely Beverly,

             

            It's definitely helpful to know that I should not search in variables in the WHERE statement. I did not know that. Unfortunately, the returned result is 0 and not 2. My field name of YEARFROM is a reserved word... well YEAR is and FROM is as separate words, but I have to escape it anyways or it errors. So, I write this as:

             

             

            ExecuteSQL (

            "SELECT Count(Name_Program)

            FROM PROGRAM

            WHERE Name_Program = 'Secure Future$ Outreach' and \"YEARFROM\" = ? and \"YEARTO\" = ? " ; $$YEARFROM ; $$YEARTO )

             

            but still no good.

            • 3. Re: ExecuteSQL
              RobertWard

              Okay. Results are good now. Bad format. I left out the row and column parts.

               

              ExecuteSQL (

              "SELECT Count(Name_Program)

              FROM PROGRAM

              WHERE Name_Program = 'Secure Future$ Outreach' and \"YEARFROM\" = ? and \"YEARTO\" = ? " ; "" ; "" $$YEARFROM ; $$YEARTO )

               

              Thanks Beverly. I appreciate your help a great deal.

              • 4. Re: ExecuteSQL
                beverly

                I'm glad you caught your error, Robert!

                 

                Something that may help: If you place EvaluationError() around your ExecuteSQL(), you will get the error code if it's not right. Only do that, if you get the dread "?" result, of course.

                 

                Beverly

                • 5. Re: ExecuteSQL
                  RobertWard

                  Thank you for the tip. I will give that a try.