2 Replies Latest reply on Dec 22, 2016 3:47 PM by Nick Austen

    Pass result of CF to ExecuteSQL parameters

    Nick Austen

      Hi all

      I am trying to create a dynamic ExecuteSQL statement, where the parameters are set up using a custom function. With no luck.

       

      The cf "FormSQLRCParams > _params" gives back the value expected (Row1_Name; From_Date; To_Date; Col1_Name).

      - when I use the cf I get an empty field  (_params)

      - if I substitute in the actual parameters (Row1_Name; From_Date; To_Date; Col1_Name) - I get the value I want (commented out below)

       

      Any help much appreciated!

       

      Calculated Field:

      Let(

      [

      _params = FormSQLRCParams ( "Location";1;1);

       

      _sqlstatement = "SELECT

      SUM(Unit_Amount)

      FROM

      Purchase_Orders_Lines

      WHERE

      Tracking_Department = ?

      AND PO_Date >= ?

      AND PO_Date <= ?

      AND

      PO_State_Short = ?"

      ];

       

      ExecuteSQL ( _sqlstatement;"";"";

      _params

      //Row1_Name; From_Date; To_Date; Col1_Name

      )

      ) //Let

        • 1. Re: Pass result of CF to ExecuteSQL parameters
          erolst

          Even if you unpack a parameter list** into several values, from the point of view of ExecuteSQL( ) this is still only one syntactical parameter. You can try something along these lines:

           

          Let ( [

            myArgList = List ( Quote ( "A" ) ; 2 ) ;

            unpackedArgs = Substitute ( myArgList ; ¶ ; " ; " ) ;

           

            sql = " \" SELECT grade FROM Grades WHERE grade = ? AND type = ? \" " ;

            sqlEval = "ExecuteSQL ( " & sql & " ; \"\" ; \"\” ; " & unpackedArgs & ")"

            ] ;

            Evaluate ( sqlEval )

          )

           

          Now you can dynamically construct the statement and the argument list.

           

          Search this forum for other examples; I remember Wim de Corte showing a few.

           

          **Really, an argument list …

          1 of 1 people found this helpful
          • 2. Re: Pass result of CF to ExecuteSQL parameters
            Nick Austen

            Thanks erolst works beautifully!

            Many thanks