7 Replies Latest reply on Nov 10, 2016 8:10 AM by ChristofferRexen_1

    ExecuteSQL Statement

    ChristofferRexen_1

      So, I've begun fiddling with ExecuteSQL statements.

      Awesome tool, I gotta say!

       

      Now, I have created this SQL statement, with minor adjustments with help from SeedCodes SQLExplorer

      // ------------  BEGIN EXECUTESQL BLOCK ------------ 

      // Built by SQLExplorer.  Compliments of SeedCode… Cheers!

       

       

      Let ( [

       

       

      //VAR

       

       

      $$invoiceID = INV_Invoice::__kpln_ID ;

      isTax = INV_Invoice_Billingmodel_Invoicelinetext::IsTax ;

       

       

       

       

      // Define Carriage Return Substitution Character

      ReturnSub = "\n" ;

       

       

      // Enable the second line here if you want the header in your results

       

       

      header = "";

      //header = "a.Amount__lcs";

       

       

       

       

      // Define Table variables

      bINVOICE = Quote ( GetValue ( Substitute ( GetFieldName ( _Invoice::__kpln_ID ) ; "::" ; ¶ ) ; 1 ) ) & " b" ;

      aINVOICELINE = Quote ( GetValue ( Substitute ( GetFieldName ( _Invoiceline::__kpln_ID ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;

       

       

      // Define Field Variables

      b__kpln_ID = "b." & Quote ( GetValue ( Substitute ( GetFieldName ( _Invoice::__kpln_ID ) ; "::" ; ¶ ) ; 2 ) ) ;

      a_kfln_InvoiceID = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( _Invoiceline::_kfln_InvoiceID ) ; "::" ; ¶ ) ; 2 ) ) ;

      aIsTax = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( _Invoiceline::IsTax ) ; "::" ; ¶ ) ; 2 ) ) ;

      aAmount__lcs = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( _Invoiceline::Amount__lcs ) ; "::" ; ¶ ) ; 2 ) ) ;

       

       

      // Build SQL Query

      q =

      "SELECT " & aAmount__lcs & "

      FROM " & aINVOICELINE & "

      INNER JOIN " & bINVOICE & " ON " & a_kfln_InvoiceID & " = " & b__kpln_ID & "

      WHERE " & a_kfln_InvoiceID & " = ? AND " & aIsTax & " = ? "  ;

       

       

      // Run SQL Query

      result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; $$invoiceID ; "1" ) ] ; 

       

       

      // Clean up carriage returns

       

       

      List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  ) )

       

       

      // RESULT

       

       

       

       

      )

      It works like a charm, almost, since it finds the desired related Taxes from the Invoiceline table.
      Although since there are 3 records, each with a Summary Field on, it shows all 3 records/summary fields in the search.

      How do I narrow this down to only showing 1 record / 1 summary field in my SQL statement?

       

      Now I could modify the SQL statement, so the statement finds the Amount fields instead of the Amount__lcs fields (summary field)

      Then I get the desired 3 records, but then my problem would be how to summarize the found set in the same SQL statement?

       

      Could anyone bring me further in my SQL quest?

       

      Best regards

      Christoffer

        • 1. Re: ExecuteSQL Statement
          philmodjunk

          Use

           

          sum ( amount )

           

          after SELECT

           

          To get a single total based on the records matching your where criteria.

          1 of 1 people found this helpful
          • 2. Re: ExecuteSQL Statement
            erolst

            The SQL engine doesn't recognise/honour any relationships or found sets (or summary field results based on these found set).

             

            Of course, SQL brings its own tools; try

             

            SELECT " & SUM ( a.amount )

             

            which means you need to redo the variable assignment and point at the amount field.

            1 of 1 people found this helpful
            • 3. Re: ExecuteSQL Statement
              ChristofferRexen_1

              I'm afraid I don't quite get the context.

               

              // ------------  BEGIN EXECUTESQL BLOCK ------------ 

              // Built by SQLExplorer.  Compliments of SeedCode… Cheers!

               

               

              Let ( [

               

               

              //VAR

               

               

              $$invoiceID = INV_Invoice::__kpln_ID ;

              isTax = INV_Invoice_Billingmodel_Invoicelinetext::IsTax ;

               

               

               

               

              // Define Carriage Return Substitution Character

              ReturnSub = "\n" ;

               

               

              // Enable the second line here if you want the header in your results

               

               

              header = "";

              //header = "a.Amount";

               

               

               

               

              // Define Table variables

              bINVOICE = Quote ( GetValue ( Substitute ( GetFieldName ( _Invoice::__kpln_ID ) ; "::" ; ¶ ) ; 1 ) ) & " b" ;

              aINVOICELINE = Quote ( GetValue ( Substitute ( GetFieldName ( _Invoiceline::__kpln_ID ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;

               

               

              // Define Field Variables

              b__kpln_ID = "b." & Quote ( GetValue ( Substitute ( GetFieldName ( _Invoice::__kpln_ID ) ; "::" ; ¶ ) ; 2 ) ) ;

              a_kfln_InvoiceID = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( _Invoiceline::_kfln_InvoiceID ) ; "::" ; ¶ ) ; 2 ) ) ;

              aIsTax = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( _Invoiceline::IsTax ) ; "::" ; ¶ ) ; 2 ) ) ;

              aAmount = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( _Invoiceline::Amount ) ; "::" ; ¶ ) ; 2 ) ) ;

               

               

              // Build SQL Query

              q =

              "SELECT " & SUM ( aAmount ) & "

              FROM " & aINVOICELINE & "

              INNER JOIN " & bINVOICE & " ON " & a_kfln_InvoiceID & " = " & b__kpln_ID & "

              WHERE " & a_kfln_InvoiceID & " = ? AND " & aIsTax & " = ? "  ;

               

               

              // Run SQL Query

              result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; $$invoiceID ; "1" ) ] ; 

               

               

              // Clean up carriage returns

               

               

              List ( header ; Substitute ( result ; [ ¶ ; ReturnSub ] ; [ "|*|" ; ¶ ]  ) )

               

               

              // RESULT

               

               

               

              )

              If I try this, after changing the field variables to Amount, I get FM's Custom Error Message:

              "The specified field cannot be found"

               

              Am I totally of track?

              • 4. Re: ExecuteSQL Statement
                erolst

                Try changing

                 

                "SELECT " & SUM ( aAmount ) & "

                 

                to

                 

                "SELECT SUM (" & aAmount & ")

                1 of 1 people found this helpful
                • 5. Re: ExecuteSQL Statement
                  PeterDoern

                  in the OP's case, would actually need to be:

                   

                  "SELECT SUM ( " & aAmount__lcs & ")

                  • 6. Re: ExecuteSQL Statement
                    PeterDoern

                    My mistake... didn't see that you'd renamed the variables. Never mind, nothing to see here.

                    • 7. Re: ExecuteSQL Statement
                      ChristofferRexen_1

                      That did the trick, you are a genius!

                      Thank you erolst, much appreciated!