View Issue Details

IDProjectCategoryView StatusLast Update
0001744Database Comparer utilityGeneralpublic2022-04-15 11:21
ReporterZento Assigned To 
PrioritynormalSeveritymajorReproducibilityalways
Status newResolutionopen 
Summary0001744: Error parsing stored procedure with MERGE INTO inside
DescriptionSeems to be some problem parsing stored procedure bodies with MERGE INTO sentences inside:

10:10:22.206 Parsing bodies...
10:10:22.206 Error: Procedure SALDONIVEL: Must be: USING
Script: Line:10 Pos:13

Same error using FB 2.5 or 3.0

Example failing stored procedure:

create or alter procedure SALDONIVEL (
    CODEMPRESA CODEMPRESA,
    EJERCICIO EJERCICIO,
    DESDE date,
    HASTA date,
    NIVEL integer)
returns (
    CODIGO varchar(15),
    SALDODEBE numeric(18,2),
    SALDOHABER numeric(18,2),
    SALDODEBEINI numeric(18,2),
    SALDOHABERINI numeric(18,2),
    SALDODEBEAPE numeric(18,2),
    SALDOHABERAPE numeric(18,2),
    SALDODEBEREGCIE numeric(18,2),
    SALDOHABERREGCIE numeric(18,2))
as
begin
  DELETE FROM TmpSaldoNivel WHERE Codigo IS NOT NULL;

  INSERT INTO TmpSaldoNivel(Codigo)
    SELECT DISTINCT SUBSTRING(Subcuentas.Codigo FROM 1 FOR :Nivel)
    FROM Subcuentas
    WHERE Subcuentas.CodEmpresa = :CodEmpresa AND Subcuentas.Ejercicio = :Ejercicio;

  MERGE INTO TmpSaldoNivel
    USING (
      SELECT SUBSTRING(PartDebe.SubcuentaDebe FROM 1 FOR :Nivel) AS Codigo, SUM(ImporteDebe) AS SaldoDebe
        FROM Partidas PartDebe
        LEFT OUTER JOIN Asientos AtoDebe ON (AtoDebe.Codigo = PartDebe.CodigoAsiento AND AtoDebe.CodEmpresa = PartDebe.CodEmpresa AND AtoDebe.Ejercicio = PartDebe.Ejercicio)
        WHERE PartDebe.CodEmpresa = :CodEmpresa AND PartDebe.Ejercicio = :Ejercicio
            AND AtoDebe.Fecha >= :Desde AND AtoDebe.Fecha <= :Hasta AND (COALESCE(AtoDebe.Utilidad, '') NOT IN ('APE', 'REG', 'CIE'))
        GROUP BY SUBSTRING(PartDebe.SubcuentaDebe FROM 1 FOR :Nivel)
    ) SaldoDebe
    ON (SaldoDebe.Codigo = TmpSaldoNivel.Codigo)
    WHEN MATCHED THEN UPDATE
      SET TmpSaldoNivel.SaldoDebe = SaldoDebe.SaldoDebe;

  /* CODE REMOVED FOR READABILITY */

  FOR
    SELECT Codigo, SaldoDebe, SaldoHaber, SaldoDebeIni, SaldoHaberIni, SaldoDebeApe, SaldoHaberApe, SaldoDebeRegCie, SaldoHaberRegCie
      FROM TmpSaldoNivel
      ORDER BY Codigo
  INTO :Codigo, :SaldoDebe, :SaldoHaber, :SaldoDebeIni, :SaldoHaberIni, :SaldoDebeApe, :SaldoHaberApe, :SaldoDebeRegCie, :SaldoHaberRegCie
  DO
    SUSPEND;
end^
TagsNo tags attached.

Activities

Zento

2022-04-15 11:19

reporter   ~0004577

Seems related to 0001662. Tried with Database Comparer versions 7.1.0.1691 and 8.0.0.2458, with same result.

Issue History

Date Modified Username Field Change
2022-04-15 11:17 Zento New Issue
2022-04-15 11:19 Zento Note Added: 0004577