Procedimiento con condiciones dinamicas

[mjjj]

Estimados, utilizo Firebird 2.5, y tengo el siguiente procedimiento almacenado llamado inventario_resumen, con la variable de entrada bodegas de tipo varchar:

Código SQL [-]
begin

for select id_item, i_s.nombre, i_s.id_clasificacion from insumos_servicios I_S
INNER JOIN clasificaciones c on c.id_clasificacion = i_s.id_clasificacion
where que_es = 'T' AND estructura = 0 into :id_item, :nombre, :id_clasificacion do
begin
execute statement
'select coalesce(sum(cantidad * precio)/sum(iif(cantidad = 0,1,cantidad)),0),
coalesce(sum(cantidad),0) from inventario
where id_item ='||:id_item||' and id_bodega in '||(:bodegas)
into recio_medio, :cantidad_ingreso;


execute statement
'select coalesce(avg(precio),0), coalesce(sum(cantidad),0) from inventario_egreso
where id_item ='||:id_item||' and id_bodega in '||(:bodegas)
into recio_egreso, :cantidad_egreso;

suspend;
end
end

el cual lo llamo se la siguiente forma:

Código SQL [-]
select id_item, nombre, cantidad_ingreso - cantidad_egreso saldo, precio_medio,
(cantidad_ingreso - cantidad_egreso) * precio_medio subtotal from inventario_resumen (:bodegas)
where cantidad_ingreso > cantidad_egreso

donde bodegas tienes el formato por ejemplo: (1,2,5), que serian los id de las bodegas a consultar.
EL problema es que la condiciones de busqueda las hago al resultado de la consulta al agregarle por ejemplo estas lineas a la llamada del procedimiento.

Código SQL [-]
and id_clasificacion =:id_clasificacion
and nombre like :nombre

... pero de igual forma el procedimiento recorre cada uno de los registros de la tabla "insumos_servicios"

He pensado en agregar mas variables de entraqda al procedimiento, tal que las condiciones se realicen directamente a la consulta original y así evitar tener que procesar todos los registros de la tabla "insumos_servicios"
Hasta aquí todo va bien, pero surge un problema, que ocurre si el usuario desea listar todos los registros, las nuevas condiciones no podría sacarlas del query original, y estaría obligado siempre tener alguna condicioin, ya que no podria, por ejemplo, quitar la linea del procedimiento

Código SQL [-]
and nombre like :nombre

Espero se entienda y me puedan ayudar a resolver esto.
Gracias

[Casimiro Notevi]

¿Por qué usas 'execute statement'?

[mjjj]

porque el usuario puede elegir revisar los items de mas de una bodega, de esta forma la condición es IN, y la forma de ingresar este parámetro al procedimiento es por una cadena, y si intento ejecutar el procedimiento con el parametro de entrada "bodega" directamente me arroja un error, por esto ejecuto cadena completa incluyendo la condicion IN como una gran cadena.

Pero finalmente esto no me arroja ningun problema.
He pensado en hacer dos procedimientos, uno con condiciones y otro sin... espero poder resolverlo de una mejor manera.
Otra idea es pasar el query completo como una variable de entrada, que incluya las condiciones que quiera y ejecutarlo todo como una gran cadena... sera la mejor ?

Gracias

[Casimiro Notevi]

Pienso que no es la forma de hacer eso. Es como si se pincha la rueda del coche y en lugar de arreglarla le haces un "invento"

[mjjj]

completamente de acuerdo, como lo harías??

[Casimiro Notevi]

Pues como tú mismo has indicado, pasándole los parámetros necesarios.
Así, a simple vista, no creo que necesites el "execute statement".

[mjjj]

Bueno lo voy a revisar, pero sin consulta no era respecto a esa parte del procedimiento, sino es al momento de condicionar las busqueda del query general, que consulta a la tabla "insumos_servicios". Adjunto procedimiento modificado.

Código SQL [-]
begin

for select id_item, i_s.nombre, i_s.id_clasificacion from insumos_servicios I_S
INNER JOIN clasificaciones c on c.id_clasificacion = i_s.id_clasificacion
where que_es = 'T' AND estructura = 0 
and nombre like :nombre /* nueva linea */
into :id_item, :nombre, :id_clasificacion do
begin
execute statement
'select coalesce(sum(cantidad * precio)/sum(iif(cantidad = 0,1,cantidad)),0),
coalesce(sum(cantidad),0) from inventario
where id_item ='||:id_item||' and id_bodega in '||(:bodegas)
into recio_medio, :cantidad_ingreso;


execute statement
'select coalesce(avg(precio),0), coalesce(sum(cantidad),0) from inventario_egreso
where id_item ='||:id_item||' and id_bodega in '||(:bodegas)
into recio_egreso, :cantidad_egreso;

suspend;
end
end

Esto funciona bien siempre y cuando el usuario desee buscar por algún items en especifico, sin embargo, imaginense que el usuario desea revizar la lista completa de items, la nueva condición me impediría hacer esto, ya que si o si es necesario pasar este valor en el query original.

Nombre es una nueva variable de entrada.
Pueden existir mas de una nueva condicion de busqueda.

Se les ocurre como solucionar esto?

[Casimiro Notevi]

Empezado por mjjj

sin embargo, imaginense que el usuario desea revizar la lista completa de items

Esa parte no la entiendo.

[mjjj]

es el caso que el usuario desee revisar la lista completa de items, es decir, la nueva linea del procedimiento debiese eliminarse o no considerarse, para que no filtre por esta condición.

El tema es que no modificar en tiempo de ejecución el procedimiento según sea el requerimiento del usuario, ese es el tema principal !!!!

[Casimiro Notevi]

Creo que estoy dormido, porque no me entero que quiéres decir

es el caso que el usuario desee revisar la lista completa de items,
¿Qué lista, qué items?

es decir, la nueva linea del procedimiento debiese eliminarse o no considerarse, para que no filtre por esta condición.
¿La nueva línea?, ¿qué es la 'nueva línea'?, ¿eliminarse?

El tema es que no modificar en tiempo de ejecución el procedimiento según sea el requerimiento del usuario, ese es el tema principal !!!!
Sigo sin entender.

Llámame torpe, pero no lo comprendo

[mjjj]

si te fijas, el segundo código del procedimiento que inserté en el foro, tiene una nueva linea... "and nombre like :nombre" /* nueva linea */, donde nombre es una nueva variable de entrada. Con este he solucionado el problema de buscar un registro en particular de la tabla insumos_servicios, que es el FOR del procedimiento (encabezado).

El problema surge cuando el usuario quiere revisar todos los registros de la tabla insumos_servicios, y con esta condición en el procedimiento no podrá realizarlo.

El tema es como pasarle al procedimiento las condiciones necesarias para buscar lo que el usuario desea, todos los registros o algunos registros, siendo que la unica forma es cambiando las propias lineas procedimiento (las condiciones, por ejemplo "and nombre like :nombre").

Espero se entienda.
Gracias

[Casimiro Notevi]

Pues si el parámetro tiene un valor determinado (-1, en blanco, o lo que quieras) haces una cosa o haces otra.

[[fjcg02]]

Hola,
así a bote pronto creo que puedes sustituir el procedimiento almacenado por una vista. Después podrías hacer los filtros que quieras.
Para ello tendrías que incluir el campo id_bodega en la vista, ya que el campo id_clasificación ya lo tienes.

En definitiva, haz una vista con la consulta de las tablas insumos_servicios, clasificaciones, inventario, inventario_egreso, utiliza funciones de agregado y agrupa por id_item.

Código SQL [-]
select i_s.id_item, max(i_s.nombre) AS NOMBRE, max(i_s.id_clasificacion) AS ID_CLASIFICACION , coalesce(sum(inv.cantidad * inv.precio)/sum(iif(inv.cantidad = 0,1, 
          inv.cantidad)),0) AS PRECIO_MEDIO, coalesce(sum(inv.cantidad),0) AS CANTIDAD_INGRESO,
          coalesce(avg(inv_e.precio),0) AS PRECIO_EGRESO, coalesce(sum(inv_e.cantidad),0) AS CANTIDAD_EGRESO, max(id_bodega) AS ID_BODEGA

from insumos_servicios I_S
INNER JOIN clasificaciones c on c.id_clasificacion = i_s.id_clasificacion
INNER JOIN inventario inv on id_item = i_s.id_item
INNER JOIN inventario_egreso inv_e on id_item = i_s.id_item

where i_s.que_es = 'T' AND i_s.estructura = 0 
GROUP BY i_s.id_item

Te lo he puesto de memoria, seguro que hay algún fallo desintaxis.
Una vez tengas la vista, montas el filtro de las bodegas y/o agrupas por ellas.

Espero que te sirva.

Un saludo

[mjjj]

Estimados, la idea expuesta por fjcg02, podrías haber funcionado, pero tenia un problema es que el id_bodega está en 2 tablas.

El tema lo resolví de la siguiente forma lo expongo por si a alguien le sirve.

Modifique el procedimiento agregando una segunda variable de entrada de tipo varchar (1000), llamada texto. Esta la creo en tiempo de ejecucción con todas das las condiciones deseadas por el usuario.

Código SQL [-]
create or alter procedure INVENTARIO_RESUMEN (
    BODEGAS varchar(150),
    TEXTO varchar(1000) = 0)
returns (
    ID_ITEM bigint,
    NOMBRE varchar(80),
    PRECIO_MEDIO numeric(15,2),
    CANTIDAD_INGRESO numeric(15,2),
    CANTIDAD_EGRESO numeric(15,2),
    ID_CLASIFICACION bigint,
    PRECIO_EGRESO numeric(15,2))
as
begin

for execute statement :texto into :id_item, :nombre, :id_clasificacion do
begin
execute statement
'select coalesce(sum(cantidad * precio)/sum(iif(cantidad = 0,1,cantidad)),0),
coalesce(sum(cantidad),0) from inventario
where id_item ='||:id_item||' and id_bodega in '||(:bodegas)
into recio_medio, :cantidad_ingreso;

execute statement
'select coalesce(avg(precio),0), coalesce(sum(cantidad),0) from inventario_egreso
where id_item ='||:id_item||' and id_bodega in '||(:bodegas)
into recio_egreso, :cantidad_egreso;

suspend;
end
end

Funciona perfecto, y mejora notablemente la velocidad, ya que el query principal del procedimiento es sobre un numero limitado de registro, dadas las condiciones requeridas.

Saludos.