Hi All,

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.

At the end (with deadlines quickly coming at us...) we choose to rewrote the query and remove the last nested query, and the function is working fine now.



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

  EXECUTE IMMEDIATE
  'SELECT sum((pe.cantidad_inicial + pe.ingresos) - pe.salidas)
  INTO ?
  FROM inv_producto_existencia pe
  INNER JOIN
  (
     SELECT   a.id_producto_empresa,
              a.id_ubicacion,
              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
  ON
  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);

END
VERSION 1.00!

Regards,
Mario

Mario,

<< 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. >>

Please send me your database (catalog also, please) along with the values
that you're testing with, and I'll see what the issue is.

Thanks,

Tim Young
Elevate Software
www.elevatesoft.com

<< Mario,

Please send me your database (catalog also, please) along with the values
that you're testing with, and I'll see what the issue is.>>

Tim, I just sent you the backup to your email address.

Regards,
Mario