AnsweredAssumed Answered

ExecuteSQL Statement

Question asked by ChristofferRexen_1 on Nov 9, 2016
Latest reply on Nov 10, 2016 by 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

Outcomes