okramis

ExecuteSQL sub-selects FMPro 15

Discussion created by okramis on May 11, 2016
Latest reply on May 9, 2017 by okramis

In FMP15 generating cross tab reports with ExecuteSQL and sub-selects results in question mark, where the same query on FMP12, 13, 14 works flawless.

Example file here: http://kevinfrank.com/fmh/Outer-Join-Demo-7.zip

 

Query inside script "refresh week view":

 

Let (  [ d1 = dev::date_r[1] ; d2 = dev::date_r[2] ; d3 = dev::date_r[3] ; d4 = dev::date_r[4] ;

         d5 = dev::date_r[5] ; d6 = dev::date_r[6] ; d7 = dev::date_r[7] ] ;

ExecuteSQL (  

"SELECT employees.name_full ,

( SELECT SUM ( sales.amount ) FROM sales WHERE sales.date_of_sale = ? AND sales.id_employee = employees.id ) ,

( SELECT SUM ( sales.amount ) FROM sales WHERE sales.date_of_sale = ? AND sales.id_employee = employees.id ) ,

( SELECT SUM ( sales.amount ) FROM sales WHERE sales.date_of_sale = ? AND sales.id_employee = employees.id ) ,

( SELECT SUM ( sales.amount ) FROM sales WHERE sales.date_of_sale = ? AND sales.id_employee = employees.id ) ,

( SELECT SUM ( sales.amount ) FROM sales WHERE sales.date_of_sale = ? AND sales.id_employee = employees.id ) ,

( SELECT SUM ( sales.amount ) FROM sales WHERE sales.date_of_sale = ? AND sales.id_employee = employees.id ) ,

( SELECT SUM ( sales.amount ) FROM sales WHERE sales.date_of_sale = ? AND sales.id_employee = employees.id ) 

FROM employees

ORDER BY employees.name_full"

; "•" ; "" ; d1 ; d2 ; d3 ; d4 ; d5 ; d6 ; d7

)   //   end executesql

)   //   end let

 

Best regards

Otmar Kramis

Outcomes