Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 3 of 3 total |
Is there a limit on parameters and nested query's? |
Fri, Apr 26 2013 12:09 PM | Permanent Link |
Mario Enríquez Open Consult | 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 |
Mon, Apr 29 2013 12:35 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
Mon, Apr 29 2013 2:21 PM | Permanent Link |
Mario Enríquez Open Consult | << 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 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |