1 2 Previous Next 25 Replies Latest reply on May 9, 2017 8:06 AM by okramis

    ExecuteSQL sub-selects FMPro 15

    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

        1 2 Previous Next