What I thought was a simple margin calculation in ExecuteSQL.......

[Stu412]

Hi there

I HOPE this is in the right place...

I'm still trying to wrap my head around the FM SQL syntax and to a certain extent have had success.  I'm preparing some chart information and need to show a margin percentage over a period of time.  This would be calculated as (Income-Cost)/Income/*100.  The ExecuteSQL statements at the end of the code should give me 5 percentages to compare over a period of time.

My income and cost records are defined clearly in the data table each one having a text tag of either INC or COST, so DataTable::Amount records are either INC or COST.  In turn, these are split into two other columns so I can summarise Income and Cost elsewhere in the system.  I want to use these columns now to generate a percentage on a chart.

I need to somehow get that into a SQL statement - the statement below doesn't work (I believe because I'm querying the derived fields of INC and COST which seems ridiculous that you cannot do this, so probably it must be me making an error).

If I were to do Select SUM(Amount), this works fine as Amount is simply a number field?

Thanks in advance for any help  

Let ([

CUST = Customer_Home::Cust_PK;
SQL = "Select (SUM(INC)-SUM(COST))/Sum(INC) from DataTable where

CustID_FK=? and
GroupID = ? and
PeriodNumber=?"];

ExecuteSQL(SQL;"";"";CUST;1;5) & "¶" &
ExecuteSQL(SQL;"";"";CUST;1;4) & "¶" &
ExecuteSQL(SQL;"";"";CUST;1;3) & "¶" &
ExecuteSQL(SQL;"";"";CUST;1;2) & "¶" &
ExecuteSQL(SQL;"";"";CUST;1;1) & "¶"

)

[eos]

Unfortunately, it seems that the current implementation of ExecuteSQL() does not support mathematical operations with aggregates.

Some workarounds:

• use summary fields and calculations and get these values from a sorted found set

• write a script like

Set Variable [ $$resultList ; "" ]
Loop
  Exit Loop if Let ( $i = $i + 1 ; $i > 5 ) ]
  Set Variable [ $sumInc ; SQL code for sum aggregate from period $i ]
  Set Variable [ $sumCost ; SQL code for cost aggregate from period $i ]
  Set Variable [ $currentResult ; ( $sumInc - $sumCost ) / $sumInc ]
  Set Variable [ $$resultList ; List ( $$resultList ; $currentResult ) ]
End Loop
# [ use $$resultList as chart source ]

• write a recursive function that does the same

btw, if that syntax did work, you could write the calculation more succinctly as

Let ( [
  sql = "
    SELECT ( SUM ( INC ) - SUM ( COST ) ) / Sum ( INC )
    FROM DataTable
    WHERE
      CustID_FK = ? AND
      GroupID = 1 AND
      PeriodNumber BETWEEN 1 AND 5
      GROUP BY PeriodNumber 
  "
  ] ; 
  ExecuteSQL ( sql ; "" ; "" ; Customer_Home::Cust_PK )
)

Edited August 14, 2015 by eos

[otmar]

Let ([

CUST = Customer_Home::Cust_PK;
SQL = "Select (SUM(INC)-SUM(COST))/Sum(INC) from DataTable where

CustID_FK=? and
GroupID = ? and
PeriodNumber=?"];

ExecuteSQL(SQL;"";"";CUST;1;5) & "¶" &
ExecuteSQL(SQL;"";"";CUST;1;4) & "¶" &
ExecuteSQL(SQL;"";"";CUST;1;3) & "¶" &
ExecuteSQL(SQL;"";"";CUST;1;2) & "¶" &
ExecuteSQL(SQL;"";"";CUST;1;1) & "¶"

)

You could  generate a calculation-string by ExecuteSQL and let the calculation be done through Evaluate() like this:

Let ([

CUST = Customer_Home::Cust_PK;
SQL = "SELECT '(', SUM(INC), '-', SUM(COST), ')/', Sum(INC) FROM DataTable WHERE

CustID_FK=? and
GroupID = ? and
PeriodNumber=?"];

Evaluate ( ExecuteSQL ( SQL; " " ; "" ; CUST ; 1 ; 5 ) ) & "¶" &
Evaluate (ExecuteSQL ( SQL ; " " ; "" ; CUST ; 1 ; 4) ) & "¶" &
Evaluate (ExecuteSQL ( SQL ; " " ; "" ; CUST ; 1 ; 3 ) ) & "¶" &
Evaluate (ExecuteSQL ( SQL ; " " ; "" ; CUST ; 1 ; 2 ) ) & "¶" &
Evaluate (ExecuteSQL ( SQL ; " " ; "" ; CUST ; 1 ; 1 ) ) & "¶"

)

Regards

Otmar

[eos]

If calculating with aggregate results isn't supported, how will the use of Evaluate() change that?

Also, your proposed query string doesn't produce valid SQL.

[otmar]

because it's not the sql-engine doing the calculation. ExecuteSQL() brings back a string (6 columns separated by a blank):

"( value1 - value2 )/ value1" // where value1 is the aggregated value SUM(INC) and value2 SUM(COST)

and this string can very probably be "evaluated" by Evaluate()

btw: did you test it?

[eos]

Right; I was interpreting this as Evaluat()ing the ExecuteSQL() function, rather than it's string result .

While this is really nice, it seem it's not scalable with grouping; you'd have to query every sub-group.

btw: I did test this, but maybe it was too late … anyway, I didn't do it properly. Mea culpa.

[otmar]

Right; I was interpreting this as Evaluat()ing the ExecuteSQL() function, rather than it's string result .

While this is really nice, it seem it's not scalable with grouping; you'd have to query every sub-group.

btw: I did test this, but maybe it was too late … anyway, I didn't do it properly. Mea culpa.

no problem ;-)

The problem with a group by result could be handled with loop by evaluating each row of the result or a recursive custom function like i.e. EvaluateList ( _list ). I haven't searched in briandunning.com, there might be something, but it wouldn't be hard to do.

I prefer to use a virtual list for the first result and do the calculation in a 2nd sql-query on the VL.

[Stu412]

Thanks for your help on this everyone, I have this running in a script now and it's working pretty well.

I have just one question surround a date field.  I need to use the same WHERE criteria to select a date from the parent table to a date variable which I use elsewhere.

I'm having trouble with this, FM suggesting the syntax is wrong by returning a '?' .  The varied query which is a calulation for a Set Variable statement is:

Let ([

CUST = Customer_Home::Cust_PK;
SQL = "SELECT date FROM DataTable WHERE

CustID_FK=? and
GroupID = ? and
PeriodNumber=?"];

However, when I place this variable in a field using Set Field [$Var], I get a '?'

Not sure what's happening with the date here?

Thanks

 

[otmar]

Thanks for your help on this everyone, I have this running in a script now and it's working pretty well.

I have just one question surround a date field.  I need to use the same WHERE criteria to select a date from the parent table to a date variable which I use elsewhere.

I'm having trouble with this, FM suggesting the syntax is wrong by returning a '?' .  The varied query which is a calulation for a Set Variable statement is:

Let ([

CUST = Customer_Home::Cust_PK;
SQL = "SELECT date FROM DataTable WHERE

CustID_FK=? and
GroupID = ? and
PeriodNumber=?"];

However, when I place this variable in a field using Set Field [$Var], I get a '?'

Not sure what's happening with the date here?

date is a reserved word in FM SQL, so you have to quate it like this:

SELECT \"date\" FROM ..... (the backslash is needed to mask/escape the " character inside a string)

have a look here: http://help.filemaker.com/app/answers/detail/a_id/12097/~/reserved-words-in-filemaker-pro

[Stu412]

Thanks for the assist Otmar

Still having problems even when it's escaped (or when I changed the field name completely).

Speculating this may be a date format thing I'm unaware of?

Thanks