2 Replies Latest reply on Aug 16, 2015 7:56 PM by camberol

    Problem with Robust Field and Table Names in SQL

    camberol

      I am trying to use the Custom Functions SQLFieldName and SQLTableName.

       

      The following query works fine until I use the Let variables.

       

      Let ( [

          ~shipper = Invoices::Shipper ;

          ~f1 = SQLFieldName ( Invoices::ShipMethod ) ;

          ~f2 = SQLFieldName ( Invoices::Shipper ) ;

          ~t1 = SQLTableName ( Invoices::Shipper ) ;

          ~query = "

              SELECT DISTINCT ShipMethod

              FROM Invoices

              WHERE Shipper = ?

              ORDER BY ShipMethod

          " ] ;

          ExecuteSQL ( ~query ; "" ; "" ; ~shipper )

      )

       

      Any suggestions on the easiest way to do this or some other method for robust naming of fields and tables?

       

      Thank you,

      Camberol

        • 1. Re: Problem with Robust Field and Table Names in SQL
          erolst

          camberol wrote:

          The following query works fine until I use the Let variables.

           

          Does “using the Let() variables” mean something like:

           

          Let ( [

              ~shipper = Invoices::Shipper ;

              ~f1 = SQLFieldName ( Invoices::ShipMethod ) ;

              ~f2 = SQLFieldName ( Invoices::Shipper ) ;

              ~t1 = SQLTableName ( Invoices::Shipper ) ;

              ~query = "

                  SELECT DISTINCT ~f1

                  FROM ~t1

                  WHERE Shipper = ?

                  ORDER BY ~f1

              " ] ;

              ExecuteSQL ( ~query ; "" ; "" ; ~shipper )

          )

           

          That wouldn't work because inside the SQL string, these aren't Let() variables to be resolved, but just part of a “dumb” string – which you can check by returning ~query as the Let() result.

           

          Try

           

          Let ( [

            ~shipper = Invoices::Shipper ;

            ~f1 = SQLFieldName ( Invoices::ShipMethod ) ;

            ~f2 = SQLFieldName ( Invoices::Shipper ) ;

            ~t1 = SQLTableName ( Invoices::Shipper ) ;

            ~query = "

                SELECT DISTINCT " & ~f1 & "

                FROM " & ~t1 & "

                WHERE Shipper = ?

                ORDER BY " & ~f1

          ] ;

            ExecuteSQL ( ~query ; "" ; "" ; ~shipper )

          )

          • 2. Re: Problem with Robust Field and Table Names in SQL
            camberol

            erolst,

             

            Thank you