Icon View Incident Report

Minor Minor
Reported By: Mario Enriquez
Reported On: 4/30/2013
For: Version 2.12 Build 2
# 3837 Nested Sub-Queries Containing Parameters Within Derived Tables Can Cause Incorrect Results

I've run into some confusion regarding the use of parameters and nested query's on EDB.

I've wrote the following function and it compiles and runs without errors, but it does not return any the expected result.

After some debug and head scratching I found out the the parameter "pIdSucursal" that is used in the inner most query wasn't taking the correct value.

I run the same query on EDB Manager using hard coded values and the result was the expected. I wonder if I've hit some kind of limit regarding parameter and nested query's, or maybe this is a bug. We're using EDB 2.12 b2, by the way.

CREATE FUNCTION "fnInvExistenciaSucursal" (IN pIdProductoEmpresa GUID, IN pIdSucursal GUID, IN pFechaMov DATE) RETURNS DECIMAL(19,4)
  DECLARE Result DECIMAL(19,4);

  'SELECT sum((pe.cantidad_inicial + pe.ingresos) - pe.salidas)
  INTO ?
  FROM inv_producto_existencia pe
     SELECT   a.id_producto_empresa,
              MAX(a.fecha) fecha
     FROM     inv_producto_existencia   a
     WHERE   a.id_producto_empresa      =   ? AND
             a.id_ubicacion IN
              (SELECT id_ubicacion FROM inv_bodega_ubicacion WHERE id_bodega IN
                 (SELECT id_bodega FROM inv_bodega WHERE id_sucursal = ?))
             AND a.fecha <= ?
     GROUP BY id_producto_empresa, id_ubicacion
  ) AS uf
  pe.id_producto_empresa = uf.id_producto_empresa AND
  pe.id_ubicacion   = uf.id_ubicacion AND
  pe.fecha = uf.fecha
  GROUP BY pe.id_producto_empresa'

  USING Result, pIdProductoEmpresa, pIdSucursal, pFechaMov;

  RETURN COALESCE(Result, 0.0);


Resolution Resolution
Fixed Problem on 6/12/2013 in version 2.13 build 1

Products Affected Products Affected
ElevateDB Additional Software and Utilities
ElevateDB DAC Client-Server
ElevateDB DAC Client-Server with Source
ElevateDB DAC Standard
ElevateDB DAC Standard with Source
ElevateDB DAC Trial
ElevateDB LCL Standard with Source
ElevateDB PHP Standard
ElevateDB PHP Standard with Source
ElevateDB PHP Trial
ElevateDB VCL Client-Server
ElevateDB VCL Client-Server with Source
ElevateDB VCL Standard
ElevateDB VCL Standard with Source
ElevateDB VCL Trial