2 Replies Latest reply on Nov 13, 2014 10:16 AM by philmodjunk

    Problem with ExecuteSQL

    andretru2

      Title

      Problem with ExecuteSQL

      Post

      Hello,

      Sorry if this has been posted before but I was unable to find a similar issue (been looking for 2 hours).

      Anyways, hope this is rather an easy solution. 

      I have a text field ("sqlQuery") with a sql statement (below) and a "run" button:

      "select nameFirst 
      from People 
      where cast(id_status as varchar(25)) in ( select Parameter     
                                                                from ReportParameters  
                                                                 where fieldParameter='id_status'  
                                                                 and id_report=1) "

      The script ideally would execute this sql referencing the field such as:

      $set Variable [$sql; Value: ExecuteSQL(ReportSetup::sqlQuery;"";"")

      However, it returns a "?".

      The funny thing is that if I replace the ReportSetup::sqlQuery with the actual query, it works. 

      I've tried all kinds of things trying to mimic the quotation marks but no success (i.e. "Quote")

      Hopefully this is doable as I'm building a dynamic report builder and I want to execute whatever SQL statement is stored in the ReportSetup table.

      Thanks in advance,

       

       

       

       

        • 1. Re: Problem with ExecuteSQL
          andretru2

          Still unable to solve this problem. If anyone has any suggestions it would be greatly appreciated.

           

          Thank you,

           

          • 2. Re: Problem with ExecuteSQL
            philmodjunk

            The text in your text field should not be enclosed in any quotes of any kind.

            I've had problems as well and it seems to be the "set notation" that's the culprit. In my case, I ended up using an "in line" calculation to insert the needed data into the set between parenthesis--not an option here,but what worked for me.

            You might, as a test, see if a simpler expression in the text field that does not use "in" works correctly. If it does, we have a bug to report in Report an Issue.