Having trouble with an ExecuteSQL calculation. I used SeedCode's SQL Explorer to build the calculation. It works fine if I use literal values from a specific record; but if I instead change it to a field reference, the calculation fails. I get the message <unrelated table> in the Data Viewer. The code is below. Am I misusing the field names somehow?

 

// Build SQL Query

q =

"SELECT SUM ( " & ainterestMineralDecimal & " )

FROM " & a@LINEITEMS & "

WHERE " & a_id_lease & " = ? AND " & a_id_tract & " = ? AND " & a_id_unit & " = ? " ;

 

// Run SQL Query

result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; @LineItems::_id_lease ; @LineItems::_id_tract ; @LineItems::_id_unit ) ] ;

 

Thanks,

 

Tom RuBane

I never figured out why I couldn't use field names; but I did work around this by pushing the field contents into a local variable, and referencing the variables in the SQL query. 

You wouldn't use all those ampersands in the query -- the field and table names are part of the literal string.

I sometimes like to use this form for readability:

q = "SELECT SUM ( interest )
FROM LineItems
WHERE  a_id_lease  = ? AND  a_id_tract  = ? AND  a_id_unit  = ? " ;

Substitute( q ; [ "interest" ; "ainterestMineralDecimal" ]; [ "LineItems" ; "a@LINEITEMS" ]; etc. )

Thanks, Fitch. That was a fragment of the abstracted calculation that SQLExplorer generated; I didn't think about it not making sense outside of the whole calculation. But here's the full calculation, using variables. Still curious why I was unable to use field names to supply the field values of the current record. 

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

Let ( [

// Define Carriage Return Substitution Character

ReturnSub = "\n" ;

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

header = "";

//header = "";

// Define Table variables

a@LINEITEMS = Quote ( GetValue ( Substitute ( GetFieldName ( @LineItems::_id ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;

// Define Field Variables

a_id = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @LineItems::_id ) ; "::" ; ¶ ) ; 2 ) ) ;

a_id_lease = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @LineItems::_id_lease ) ; "::" ; ¶ ) ; 2 ) ) ;

a_id_tract = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @LineItems::_id_tract ) ; "::" ; ¶ ) ; 2 ) ) ;

a_id_unit = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @LineItems::_id_unit ) ; "::" ; ¶ ) ; 2 ) ) ;

atype = "a." & Quote ( GetValue ( Substitute ( GetFieldName ( @LineItems::type ) ; "::" ; ¶ ) ; 2 ) ) ;

// Build SQL Query

q =

"SELECT " & a_id & "

FROM " & a@LINEITEMS & "

WHERE " & a_id_lease & " = ? AND " & a_id_tract & " = ? AND " & a_id_unit & " = ? AND " & atype & " = ? " ;

// Run SQL Query

result = ExecuteSQL ( q ; Char ( 9 ) ; "|*|" ; $leaseID ; $tractID ; $unitID ; "WI" ) ] ; 

// Clean up carriage returns

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

2 hours ago, Tom R. said:

a@LINEITEMS = Quote ( GetValue ( Substitute ( GetFieldName ( @LineItems::_id ) ; "::" ; ¶ ) ; 1 ) ) & " a" ;

 

could be this. You reference this in the FROM clause, so I would think this should be GetLayoutTableName. I also use Fitch's substitute method to swap out tokens and make the SQL readable.

That does give you the table name, and adds a reference as "a" -- and all the fields are then referenced as "a.field" ... not strictly necessary here because we're only looking at one table, but it's auto-generated code so it's built to work with one or many tables.

When you're looking at the Data Viewer and showing <unrelated...> are you on a layout based on @LineItems ?