7 Replies Latest reply on Jun 25, 2014 7:26 AM by TSGal

    Error on SQL engine when using INSERT INTO <table>

    signo

      Summary

      Error on SQL engine when using INSERT INTO

      Product

      FileMaker Pro

      Version

      12 advanced

      Operating system version

      Windows 7

      Description of the issue

      When using INSERT INTO with a GROUP BY clause and 2 summary columns, an additional column is inserted for each summary field, the content of the additional columns are the last value used to calculate the summary field.

      In order to run the INSERT INTO SQL command i used two well known plugins, both gave the same result. The plugins are 2empowerFM SQL Runner Version 2.56 and BaseElements 4.0.5.

      The data is in filemaker tables imported from Visual Foxpro tables.

      Steps to reproduce the problem

      Let(
      [
         sql = "
      Insert into
        portalCobranzas
      Select
        facturas.cod_segur As cod_seguro,
        facturas.cod_poliz As cod_poliza,
        Count(Distinct  facturas.nro_fact) As facturas,
        Sum(facturas.mto_fact) As Facturado,
        0 As Saldo,
        Max(facturas.fec_fact) As Ultima,
        seguros.nom_segur As Aseguradora,
        polizas.desc_poliz As Poliza
      From
        facturas Left Outer Join
        seguros On facturas.cod_segur = seguros.cod_segur Left Outer Join
        polizas On facturas.cod_poliz = polizas.cod_poliz
      Where
        facturas.status = 'P' And
        Year(facturas.fec_fact) = 2013
      Group By
        facturas.cod_segur, facturas.cod_poliz, seguros.nom_segur,
        polizas.desc_poliz
      "
      ];
        epSQLExecute( sql )
      )

      Expected result

      See attached file

      Actual result

      See attached file

      insert-into-bug.jpg