AnsweredAssumed Answered

Error on SQL engine when using INSERT INTO <table>

Question asked by signo on Jul 13, 2013
Latest reply on Jun 25, 2014 by TSGal

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

Outcomes