SQL inventario

[mjjj]

Hola amigos, alguna idea para mejorara esto...

Tengo una consulta SQL, que demora aproximadamente 15 segundo en devolver resultados. Esto empezo a ocurrir mientras mas registro tengo en mi base de datos.

Este es el codigo que utilizo.


IBQUERY1.CLOSE;
IBQUERY1.SQL.Clear;
IBQUERY1.SQL.ADD('SELECT m.cod_insum,M.nom_insum, m.unidad, M.CRITICO, m.rotacion,');
if sComboBox1.ItemIndex <> 0 then
begin
IBQUERY1.SQL.ADD('((SELECT coalesce(sum(cantidad),0) FROM inventario I WHERE I.id=M.cod_insum and tipo ='+QUOTEDSTR('I'));
IBQUERY1.SQL.ADD('AND I.EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =(SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))-(SELECT coalesce(sum(cantidad),0) FROM inventario i WHERE I.id=M.cod_insum and (tipo ='+QUOTEDSTR('E'));
IBQUERY1.SQL.ADD('OR TIPO ='+QUOTEDSTR('T')+')');
IBQUERY1.SQL.ADD('AND I.EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =(SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))) AS SALDO, ((SELECT coalesce(sum(cantidad * precio * tc),0) FROM inventario I WHERE I.id=M.cod_insum and tipo ='+QUOTEDSTR('I'));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =(SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))- (SELECT coalesce(sum(cantidad * precio * tc),0) FROM inventario I WHERE I.id=M.cod_insum and (tipo ='+QUOTEDSTR('E'));
IBQUERY1.SQL.ADD('OR TIPO ='+QUOTEDSTR('T')+')');
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =(SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD(')))/coalesce(NULLIF((SELECT sum(cantidad) FROM inventario I WHERE I.id=M.cod_insum and tipo ='+QUOTEDSTR('I'));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =(SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))-(SELECT COALESCE(sum(cantidad),0) FROM inventario i WHERE I.id=M.cod_insum and (tipo ='+QUOTEDSTR('E'));
IBQUERY1.SQL.ADD('OR TIPO ='+QUOTEDSTR('T')+')');
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =(SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD(')),0),1) as UNITARIO, ');
IBQUERY1.SQL.ADD('((SELECT coalesce(sum(cantidad),0) FROM inventario I WHERE I.id=M.cod_insum and tipo ='+QUOTEDSTR('I'));
IBQUERY1.SQL.ADD('AND I.EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =(SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))-(SELECT coalesce(sum(cantidad),0) FROM inventario i WHERE I.id=M.cod_insum and (tipo ='+QUOTEDSTR('E'));
IBQUERY1.SQL.ADD('OR TIPO ='+QUOTEDSTR('T')+')');
IBQUERY1.SQL.ADD('AND I.EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =(SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))) AS SALDO, ((SELECT coalesce(sum(cantidad * precio * tc),0) FROM inventario I WHERE I.id=M.cod_insum and tipo ='+QUOTEDSTR('I'));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =(SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))- (SELECT coalesce(sum(cantidad * precio * tc),0) FROM inventario I WHERE I.id=M.cod_insum and (tipo ='+QUOTEDSTR('E'));
IBQUERY1.SQL.ADD('OR TIPO ='+QUOTEDSTR('T')+')');
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =(SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD(')))/coalesce(NULLIF((SELECT sum(cantidad) FROM inventario I WHERE I.id=M.cod_insum and tipo ='+QUOTEDSTR('I'));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =(SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))-(SELECT COALESCE(sum(cantidad),0) FROM inventario i WHERE I.id=M.cod_insum and (tipo ='+QUOTEDSTR('E'));
IBQUERY1.SQL.ADD('OR TIPO ='+QUOTEDSTR('T')+')');
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =(SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD(')),0),1) * ');
IBQUERY1.SQL.ADD('((SELECT coalesce(sum(cantidad),0) FROM inventario I WHERE I.id=M.cod_insum and tipo ='+QUOTEDSTR('I'));
IBQUERY1.SQL.ADD('AND I.EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =(SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))-(SELECT coalesce(sum(cantidad),0) FROM inventario i WHERE I.id=M.cod_insum and (tipo ='+QUOTEDSTR('E'));
IBQUERY1.SQL.ADD('OR TIPO ='+QUOTEDSTR('T')+')');
IBQUERY1.SQL.ADD('AND I.EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =(SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))) AS SUBTOTAL');

IBQUERY1.SQL.ADD('FROM insumos M where m.empresa ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND (M.TIPO ='+quotedstr('T'));
IBQUERY1.SQL.ADD('OR M.TIPO IS NULL)');
IBQUERY1.SQL.ADD('GROUP BY m.cod_insum,M.nom_insum, m.unidad, m.critico, m.rotacion');


Es muy larga... alguna idea para hacerla mas eficiente.

Lo que hace es:
Busca los codigos en la tabla insumos (aproximadamente 1000 registros), utiliza estos codigo en la tabla inventario dado que la suma de todos los ingresos, menos la suma de todos los egresos. (calcula la cantidad de existe de cada uno de los insumos).

Además para cada uno de los insumos calcula el precio pondera, segun la sumatoria de los ingresos por su precio, menos los egresos por su precio.

UFFFF.... bueno, el tema es que esta funcionando, pero es muy relento.... unos 15 segundo en entregar la info.

Que me dicen ustedes, valdra la pena mejorar esto?
Como lo puedo hacer?
Alguna idea?

Gracias anticipadas.

[Dark_RavenM]

Ya intentaste utilizar indices para las tablas, eso hace las busquedas mas rapidas, ademas por que no haces eso de la suma en un procedimiento por que veo que haces el mismo query como 20 por ejemplo este
SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text)

eso lo podrias ejecutar en un solo query antes que lo demas, guardarlo en una vaiable y despues utilizar la variable,

igual con este
SELECT COALESCE(sum(cantidad),0) FROM inventario i WHERE I.id=M.cod_insum and (tipo ='+QUOTEDSTR('E')

[AzidRain]

Nunca habia visto una consulta taaaaaaan larga, me parece que el diseño de la misma es deficiente ya que utilizas al parecer varias consultas anidadas. Recordemos que las consultas anidadas se realizar por CADA consulta principal que la llame, de ahí el motivo de que mientras más registros más tarda la consulta.

Una consulta bien diseñada no debe de ser afectada por el número de registros que se tengan.

Por otro lado, no siempre se puede solucionar todo con una sola consulta, a veces es necesario hacer 1 o 2 más y hacer el proceso directamente en nuestro sistema Delphi.

Revisa los selects anidados que pones para saber que bodega es la que estas consultando, me parece que ahi tienes el error.

Te recomiendo que primero hagas un solo select para obtener el id de la bodega que el usuario seleccionó y una vez obtenido, solamente sustitúyelo en todos los selects que pusiste.
Algo así:


IBQUERY1.CLOSE;
IBQUERY1.SQL.Clear;
IBQUERY1.SQL.ADD('SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text));
IBQUERY1.Open;
cve_bodega := IBQUERY.FieldByName('ID').AsInteger;
IBQUERY1.Close;
//Ya tentemos la clave de la bodega

IBQUERY1.SQL.Clear;

IBQUERY1.SQL.ADD('SELECT m.cod_insum,M.nom_insum, m.unidad, M.CRITICO, m.rotacion,');

if sComboBox1.ItemIndex <> 0 then
begin
IBQUERY1.SQL.ADD('((SELECT coalesce(sum(cantidad),0) FROM inventario I WHERE I.id=M.cod_insum and tipo ='+QUOTEDSTR('I'));
IBQUERY1.SQL.ADD('AND I.EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =:bodega';
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))-(SELECT coalesce(sum(cantidad),0) FROM inventario i WHERE I.id=M.cod_insum and (tipo ='+QUOTEDSTR('E'));
IBQUERY1.SQL.ADD('OR TIPO ='+QUOTEDSTR('T')+')');
IBQUERY1.SQL.ADD('AND I.EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =:bodega';
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))) AS SALDO, ((SELECT coalesce(sum(cantidad * precio * tc),0) FROM inventario I WHERE I.id=M.cod_insum and tipo ='+QUOTEDSTR('I'));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =:bodega';
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))- (SELECT coalesce(sum(cantidad * precio * tc),0) FROM inventario I WHERE I.id=M.cod_insum and (tipo ='+QUOTEDSTR('E'));
IBQUERY1.SQL.ADD('OR TIPO ='+QUOTEDSTR('T')+')');
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =:bodega';
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD(')))/coalesce(NULLIF((SELECT sum(cantidad) FROM inventario I WHERE I.id=M.cod_insum and tipo ='+QUOTEDSTR('I'));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =:bodega';
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))-(SELECT COALESCE(sum(cantidad),0) FROM inventario i WHERE I.id=M.cod_insum and (tipo ='+QUOTEDSTR('E'));
IBQUERY1.SQL.ADD('OR TIPO ='+QUOTEDSTR('T')+')');
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =:bodega';
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD(')),0),1) as UNITARIO, ');
IBQUERY1.SQL.ADD('((SELECT coalesce(sum(cantidad),0) FROM inventario I WHERE I.id=M.cod_insum and tipo ='+QUOTEDSTR('I'));
IBQUERY1.SQL.ADD('AND I.EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =:bodega';
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))-(SELECT coalesce(sum(cantidad),0) FROM inventario i WHERE I.id=M.cod_insum and (tipo ='+QUOTEDSTR('E'));
IBQUERY1.SQL.ADD('OR TIPO ='+QUOTEDSTR('T')+')');
IBQUERY1.SQL.ADD('AND I.EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =:bodega';
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))) AS SALDO, ((SELECT coalesce(sum(cantidad * precio * tc),0) FROM inventario I WHERE I.id=M.cod_insum and tipo ='+QUOTEDSTR('I'));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =:bodega';
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))- (SELECT coalesce(sum(cantidad * precio * tc),0) FROM inventario I WHERE I.id=M.cod_insum and (tipo ='+QUOTEDSTR('E'));
IBQUERY1.SQL.ADD('OR TIPO ='+QUOTEDSTR('T')+')');
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =(SELECT ID FROM BODEGA WHERE NOMBRE ='+quotedstr(scombobox1.text));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD(')))/coalesce(NULLIF((SELECT sum(cantidad) FROM inventario I WHERE I.id=M.cod_insum and tipo ='+QUOTEDSTR('I'));
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =:bodega';
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))-(SELECT COALESCE(sum(cantidad),0) FROM inventario i WHERE I.id=M.cod_insum and (tipo ='+QUOTEDSTR('E'));
IBQUERY1.SQL.ADD('OR TIPO ='+QUOTEDSTR('T')+')');
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =:bodega';
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD(')),0),1) * ');
IBQUERY1.SQL.ADD('((SELECT coalesce(sum(cantidad),0) FROM inventario I WHERE I.id=M.cod_insum and tipo ='+QUOTEDSTR('I'));
IBQUERY1.SQL.ADD('AND I.EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =:BODEGA';
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))-(SELECT coalesce(sum(cantidad),0) FROM inventario i WHERE I.id=M.cod_insum and (tipo ='+QUOTEDSTR('E'));
IBQUERY1.SQL.ADD('OR TIPO ='+QUOTEDSTR('T')+')');
IBQUERY1.SQL.ADD('AND I.EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND I.bodega =:bodega';
IBQUERY1.SQL.ADD('AND EMPRESA ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('))) AS SUBTOTAL');

IBQUERY1.SQL.ADD('FROM insumos M where m.empresa ='+quotedstr(form1.label1.caption));
IBQUERY1.SQL.ADD('AND (M.TIPO ='+quotedstr('T'));
IBQUERY1.SQL.ADD('OR M.TIPO IS NULL)');
IBQUERY1.SQL.ADD('GROUP BY m.cod_insum,M.nom_insum, m.unidad, m.critico, m.rotacion');

IBQUERY1.ParamByNAme('bodega').AsInteger := cve_bodega;


revisa el tema de parámetros también te puede simplificar la vida. No recuerdo si IBQUERY se comporta igual que TZQuery (de Zeos) que automáticasmente crea los parámetros de acuerdo con el código SQl que uno le ponga. Aún así todavía hay querys anidados que no les veo razón de ser.

[Bpascal]

Yo tambien pienso que ese SELECT esta muy largo y con toda la pinta de deficiente; pero para ser honesto esta muy largo que no me puse a ver todo lo que pide y si realmente esa es la unica manera de hacerlo.

Pero veo que utilizas un tan solo WHERE con todas las condiciones (amarradas usando infinidad de ANDs) y select anidados para seguramente relacionar tablas, obtener y resumir datos, y todo lo que se necesite.


Busca los codigos en la tabla insumos (aproximadamente 1000 registros), utiliza estos codigo en la tabla inventario dado que la suma de todos los ingresos, menos la suma de todos los egresos. (calcula la cantidad de existe de cada uno de los insumos).


Segun te entiendo tienes la tabla INSUMO que es el archivo maestro (o catalago de codigos donde estan todas las descripciones de los productos) y tienes una tabla inventario donde estan los movimientos que podrian o no haber de cada codigo en la tabla insumo; es decir a cada registro en insumos le aplicas un querry complicado buscando si tiene o no datos en la tabla inventario :eek: eso me suena a que equivale a tener por un lado una guia telefonica y por otro lado tienes una lista de 30 nombres para averiguar que telefono tiene cada nombre de esa lista, tu te remites al primer nombre en la guia telefonica empezando por la letra "A" y buscas si ese nombre es de alguno de los 30 que andas buscando en tu lista, y haci continuas con el siguiente nombre en la guia pasando desde la "A" a la "Z". Eso es super deficiente, lo deves de hacer al reves;

Lo primero que deves de dominar es enlazar las tablas usando indices esto se hace enlazandolas usando INNER JOIN, despues deves en UNA SOLA PASADA resumir la tabla de inventarios por codigo usando los criterios que necesites, y despues linkear ese resultado con la tabla de insumos para obtener el listado final.

[mjjj]

Esta medio complicado esto parece, les expongo otro ejemplo que me esta ocurriendo lo mismo.


IBQUERY6.Close;
IBQUERY6.SQL.CLEAR;
IBQUERY6.SQL.ADD('select area, sum(monto),');
IBQUERY6.SQL.ADD('(SELECT SUM(MONTO_COMPR) FROM COMPRAS WHERE AREA = P.AREA AND ano=:ano AND mes=:mes AND EMPRESA =:EMPRESA');
IBQUERY6.SQL.ADD('AND OFICINA ='+QUOTEDSTR(SCOMBOBOX3.TEXT));
IBQUERY6.SQL.ADD('AND ESTADO ='+QUOTEDSTR('F')+'),');
IBQUERY6.SQL.ADD('(SELECT COUNT(ESTADO) FROM COMPRAS WHERE AREA = P.AREA AND ano=:ano AND mes=:mes AND EMPRESA =:EMPRESA');
IBQUERY6.SQL.ADD('AND OFICINA ='+QUOTEDSTR(SCOMBOBOX3.TEXT));
IBQUERY6.SQL.ADD('AND ESTADO ='+QUOTEDSTR('P')+')');
IBQUERY6.SQL.ADD('from PRESUPUESTO P where EMPRESA =:EMPRESA');
IBQUERY6.SQL.ADD('AND ano=:ano AND mes=:mes');
IBQUERY6.SQL.ADD('AND OFICINA ='+QUOTEDSTR(INTTOSTR(OFI)));
IBQUERY6.SQL.ADD('group BY AREA');
IBQUERY6.parambyname('empresa').AsString:=empresa;
IBQUERY6.parambyname('ano').asinteger:=calendar1.year;
IBQUERY6.parambyname('mes').asinteger:=calendar1.month;
ibquery6.Open;


Que es lo que me entrega.

Un listado de las distintas area, con su respectivo sumatoria de montos de ordenes de compra y la cantidad de ordenes con presupuesto escedido.

Como puedo mejorar esta consulta SQL para que sea mas eficiente.

La verdada es que no entiendo muy bien como opera el iiner joi, ni menos si es que me puede servir en mi caso.

Bueno los dejo aer si me ponene un ejemplo de como quedaría mi consulta agregando alguna mejor idea.

Saludos

[Kipow]

Selects anidados por doquier, proba metiendolos en un procedimiento para minimizar el numero de selects y poder utilizar mejor los planes (PLAN). Yo tenia una consulta muy exigente tambien y opte por generar tabla temporal para la consulta pase de 2min a 2seg en el rendimiento. te estoy hablando de una consulta que ataca a mas de 6millones de registros.

[mjjj]

La verdad es que no se como hacer lo otra manera que no sea con select anidados.

Alguien me puede sugerir un codigo que consulte lo mismo pero con otra estructura.

gracias

[fjcg02]

select area, sum(monto),
SUM( CASE WHEN (OFICINA =:SCOMBOBOX3.TEXT AND ESTADO ='F') then MONTO_COMPR ELSE 0 END) AS COMPRAS,
COUNT( CASE WHEN (OFICINA =:SCOMBOBOX3.TEXT AND ESTADO ='P') THEN ESTADO ELSE 0 END) AS PENDIENTES
from PRESUPUESTO P, COMPRAS C
where P.EMPRESA=C.EMPRESA AND P.ANO = C.ANO AND P.MEs=C.MES
AND P.EMPRESA =:EMPRESA
AND P.ano=:ano
AND P.mes=:mes
AND P.OFICINA =:OFI

group BY AREA


Hola, prueba esto a ver.

1.- Relacionamos las tablas por campos comunes, porque si se hace un producto cartesiano de las tablas el nº de registros leidos se eleva exponencialmente, por lo que la demora de la obtención de los resultados también se dispara.
where P.EMPRESA=C.EMPRESA AND P.ANO = C.ANO AND P.MEs=C.MES

2.- Para sumar o contar sólamente los registros que queremos y no todos, usamos el CASE
COUNT ( -- Cuenta los registros que
CASE -- cumplan esta condición
WHEN (OFICINA =:SCOMBOBOX3.TEXT AND ESTADO ='F') --si la cumplen,
THEN suma el campo MONTO_COMPR ( importe comprado)
ELSE suma 0
END
)

3.- Ponemos los parámetros correspondientes
AND P.EMPRESA =:EMPRESA
AND P.ano=:ano
AND P.mes=:mes
AND P.OFICINA =:OFI

Estoy suponiendo que :SCOMBOBOX3.TEXT y :OFI son parametros diferentes, y logicamente SCOMBOBOX3.TEXT se debe asignar como parámetro - es por la hora , que tengo frito el cerebelo -

Ya tienes para romperte la cabeza un rato.

Lo de las bodegas es parecido, pero más largo, y no me meto con ello porque no es hora de emborracharse

Un saludo, .. ya nos dirás.
PD: Hay algun error de sintaxis, pero lógicamente, te toca hacer algo de curro ;)

[mjjj]

Por mas que trato es no funciona para nada, me entrega datos que no tienen nada que ver con lo que necesito.

Explico un poco mas la situacion.

Es una aplicacion de compras y presupuesto, la cual consta de 2 tablas, compras y presupuesto.

La tabla presupuesto contiene los campos empresa, area, subarea, ano, mes y monto. El presupuesto esta ingresado como sigue:

empresaareasubareaanomesmontoemp1apoyomantencion20091500emp1apoyomantencion20092200emp1apoyosueldo20 091100emp1apoyootro2009250emp1admgasto2009180emp2apoyomantencion2009170emp2apoyomas2009155

asi para todos las area, subarea hasta el mes de diciembre 2009

Entoces, lo que necesito es que me arroje un listado de todos las distintas area de la empresa "emp1", en donde ademas muestre la suma de todos los montos para cierto mes y año en particular (primeras 2 columnas intervienen la tablas presupuesto)

la Tabla compras es asi:
empresaareasubareaanomesmonto_comprestadoemp1apoyomantencion2009120Femp1apoyomantencion2009110Femp1a poyosueldo200912Pemp1apoyootro200916Femp1admgasto2009112Pemp2apoyomantencion2009121Pemp2apoyomas2009 11F

Puede ocurrir que no se genero ninguna compra para algun empresa, area, año y mes en particular.

Las columnas 3 y 4 de mi consulta hacen referencia a la tablas compras. En la 3 columna, debe sumar todas la compras, asociadas al empresa, area, año y mes escojida y esten finalizadas (estado F), y la 4 columna la cantidad de compras asociadas a la empresa, area, año y mes que esten excedidas (estado = P)

o que deberia arrojar seria para empresa = emp1, año=2009, mes=1

areamontocompraexcedidaapoyo600361adm8001

descarte el campo oficina, simplemente es otro parametro mas, que debe coincidir para ambas tablas.

Ayudenme con esto, ya no se que hacer.

Gracias

[mjjj]

adjunto excel con ejmplo de tablas para una mejor comprension

Saludos

[Bpascal]

Hola,

Ok, me tomé un tiempito para leer detenidamente el querry original de los insumos y creo que tengo claro que es lo que hace.

Principalmente tienes 2 tablas, INSUMOS es la tabla maestra donde estan los codigos, descripciones etc de los productos, y la tabla INVENTARIOS donde estan los movimientos de los productos tanto de entrada como de salida, la cual se diferencia por el campo tipo; "I" significa ingreso y "E" significa egreso.

La meta es basicamente listar todos los codigos de INSUMOS con su saldo en unidades y valores resumidos desde la tabla INVENTARIOS, el resumen se puede condicionar por ejemplo de que solo sea de determinada empresa, bodega o incluso solo incluyendo hasta determinada fecha si se quiere, etc.


En tu querry tu estas haciendo a la tabla de movimientos (que por naturaleza es grande) lo siguente:

- 2 veces SELECT para obtener el data saldo en unidades
- 2 veces SELECT para obtener el dato saldo en valores
- 4 veces select par obtener el dato de costo unitario.

En total 8 selects por la cada codigo de la tabla insumos, la cual tu dices tiene aprox 1000 registros, esto genera que se le apliquen aproximadamente 8,000 selects a una tabla llamada inventario que seguramente tiene 20 mil o 30 mil y creciendo por que son las transaciones.

Vemos como podemos bajar un poco la cantidad de SELECTS aplicados.

-----------------------------

Primero dejame explicarte el uso de INNER JOIN para relacionar 2 tablas, realmente es bien simple:

La tabla INVENTARIOS tiene el campo "bodega" que es el numero Id de una bodega
tambien tiene el campo "empresa" que es el numero Id de una empresa

Tienes una tabla llamada BODEGA la cual tiene su campo "ID", su campo "empresa", y su campo "nombre"

Si dedeas generar un listado de la tabla inventarios incluyendo una columna que diga el nombre de la bodega tu lo haces de la siguiente manera:


SELECT
I.id, I.cantidad, I.tipo, I.empresa I.bodega, B.nombre
FROM
INVENTARIO I, BODEGA B
WHERE
I.empresa=B.empresa AND I.bodega = B.id


Ahora re-escrito el querry usando INNER JOIN seria:

SELECT
I.id, I.cantidad, I.tipo, I.empresa I.bodega, B.nombre
FROM
INVENTARIO I
INNER JOIN BODEGA B ON I.empresa=B.empresa AND I.bodega = B.id


El resultado es exactamente lo mismo, unicamente se ha quitado la seecion WHERE y la condicion se movio a la seccion INNER JOIN.

Ahora tu te preguntaras "y que?, si es lo mismo entonces cual es la ventaja de hacerlo con WHERE ó hacerlo con INNER JOIN ?"

Usar inner join te permite liberar el WHERE de las condiciones utilizadas para enlazar tablas, muchas veces es necesario utilizar 4 o 5 tablas al mismo tiempo y obtener informacion de ellas, escribir un WHERE donde estan todas esas relaciones mas las condiciones que son para selecionar los registros deseados harian un where ya muy complicado. Utiliza la clausula WHERE solo para escribir las condiciones que se requieren para seleccionar los registros deseados en la tabla principal; y utiliza la clausula INNER JOIN para especificar la relacion que hay entre las tablas involucradas, esto ayuda al SQL a saber la mejor manera de utilizar los indices de las tablas para hacer la relacion.
------------------------------

Ok, ahora a continuacion te paso a como haria yo el querry que genere el listado en discucion;

Yo primero resumiria la tabla de inventarios utilizando UN tan solo SELECT creando una tabla temporal, esto reducira drasticamente la cantidad de select con tantas condiciones. De hecho la maneras mas optimia seria 2 tablas temporales, una por las entradas y otra por las salidas pero no quiero hacerte mas largo el post:


SELECT
id,tipo,SUM(cantidad) AS cantidad, sum(cantidad * precio * tc) as valor
FROM
Inventario I
INNER JOIN BODEGA B ON (I.bodega = B.id) and (I.empresa = B.empresa)
GROUP BY
I.id,I.tipo
WHERE
I.empresa=:numempresa AND B.nombre=:nombrebedega
INTO
#TEMP_Table


Ok, un tan solo SELECT que me genera una tabla donde ya estan resumidas las cantidades y los valores por codigo de producto y por movimiento, es decir hay tan solo un registro de cada codigo por todas las "E" y uno por todas la "I". en el where puedes agregar mas condiciones como de rango de fechas por ejemplo.

Ahora hagamos otro querry para generar el listado final enlazando la tabla INSUMOS y la tabla #temp_table:


SELECT
m.cod_insum,M.nom_insum, m.unidad, M.CRITICO, m.rotacion,
(SELECT t.cantidad from #Temp_table T WHERE t.id = m.cod_insumo AND t.tipo='I') -
((SELECT t.cantidad from #Temp_table T WHERE t.id = m.cod_insumo AND t.tipo='E') as saldo,
(SELECT t.valor from #Temp_table T WHERE t.id = m.cod_insumo AND t.tipo='I') -
((SELECT t.valor from #Temp_table T WHERE t.id = m.cod_insumo AND t.tipo='E')
as valor_saldo


Ok, aqui se ve que hay 4 SELECTS, sin envargo estos son aplicados a una tabla drasticamente reducida y las condiciones utilizadas son tribiales. Si se utilizan 2 tablas temporales una para entradas y otros para salidas entonces se reduciria a 2 SELECTS.

---------------------------------------------

Te recomiendo que le des una ojeada a la informacion sobre tablas temporales, estas se borran solas cuando se cierra la sesion, pero deveras borralas manualmente mientras la seccion este activa antes de hacer una siguiente consulta porque el SELECT INTO fallará cuando encuentre que ya hay una tabla temporal con ese nombre.


saludos.

[mjjj]

Mcuhas gracias por tu excelente y completa explicación, me quedo todo mucho mas claro, pero siempre surgen nuevas dudas.

El tema de la tablas temporales, es lo mismo que las talbas en memoria?
Estuve leyendo sobre alguno componentes que puedes servir para esto, será necesario instalar algun componente especial, o simplemente se puede generar con algun comando SQL.

Por otro lado, y gracias a la explicación del inner join, es que he tratado de implmentar esta idea a otra consultas dentro de mi aplicación y la verdad es que no me funciona nada de bien. Expongo mi código.


IBQUERY6.Close;
IBQUERY6.SQL.CLEAR;
IBQUERY6.SQL.ADD('select area, sum(monto),');
IBQUERY6.SQL.ADD('(SELECT SUM(MONTO_COMPR) FROM COMPRAS WHERE AREA = P.AREA AND ano=:ano AND mes=:mes AND EMPRESA =:EMPRESA');
IBQUERY6.SQL.ADD('AND OFICINA ='+QUOTEDSTR(SCOMBOBOX3.TEXT));
IBQUERY6.SQL.ADD('AND ESTADO ='+QUOTEDSTR('F')+'),');
IBQUERY6.SQL.ADD('(SELECT COUNT(ESTADO) FROM COMPRAS WHERE AREA = P.AREA AND ano=:ano AND mes=:mes AND EMPRESA =:EMPRESA');
IBQUERY6.SQL.ADD('AND OFICINA ='+QUOTEDSTR(SCOMBOBOX3.TEXT));
IBQUERY6.SQL.ADD('AND ESTADO ='+QUOTEDSTR('P')+')');
IBQUERY6.SQL.ADD('from PRESUPUESTO P where EMPRESA =:EMPRESA');
IBQUERY6.SQL.ADD('AND ano=:ano AND mes=:mes');
IBQUERY6.SQL.ADD('AND OFICINA ='+QUOTEDSTR(INTTOSTR(OFI)));
IBQUERY6.SQL.ADD('group BY AREA');
IBQUERY6.parambyname('empresa').AsString:=empresa;
IBQUERY6.parambyname('ano').asinteger:=calendar1.year;
IBQUERY6.parambyname('mes').asinteger:=calendar1.month;
ibquery6.Open;


lo que funciona bien, pero es relativamente lento, el mismo problema anterior, Select anidados, pero no se como hacerlo de otra manera.... ayuda porfa!!

intente utilizando esto:


select
p.area , sum(P.monto) , sum(case when estado = 'F' then monto_compr else 0 end),
count(case when estado = 'P' then monto_compr else 0 end)
from presupuesto p inner join compras c on c.area = p.area and c.ano = p.ano
and p.mes = c.mes AND P.EMPRESA = C.EMPRESA
where p.mes = '2009'
and p.ano = '1'
and P.empresa = 'emp'
AND P.oficina = '1'
AND C.OFICINA = 'SANTIAGO'
GROUP BY AREA


Me arroja valores que no tienen nada que ver, obvimante no funciona, el porqe es el que no se.

Lo que intento hacer es lo siguiente:

Un distado de todas la distintas area (tabla presupuesto), además de la sumatoria de lo compra (tabla compra estado = 'F') y la cantidad de compras excedidas (tabla compras estado ='P')

Ojala me puedan echar una manito con esta consulta.

Gracias

[fjcg02]

Bueno,
aqui tienes tu código.
Te faltaba meter la empresa en las condiciones del inner join
No se de dónde sale el parámetro oficina, que he quitado. El resto, es lo mismo que tenías, sólo tienes que comparar las querys.
El valor del parámetro empresa debe coincidir con algún valor de la tabla (emp1).
Me he basado en el juego de ensayo de la hoja de cálculo que has incluido.
Te aconsejo que juegues en una bañera primero, luego en una piscina y luego definitivamente te tires al mar. por qué? cuando hagas este tipo de consultas, hazlas sacando todos los valores, y de ahí vas filtrando poco a poco, es decir, intenta sacar los valores de las empresas primero, luego las empresas y las áreas, luego filtrando por empresa, area, .... hasta que salga lo que quieras.

select p.empresa,p.area ,
sum(P.monto) AS MONTO,
sum(case when estado = 'F' then monto_compr else 0 end) AS COMPRA,
count(case when estado = 'P' then 1 else 0 end) AS EXCEDIDA
from presupuesto p inner join compras c on c.empresa=c.empresa and c.area = p.area and c.ano = p.ano
and p.mes = c.mes AND P.EMPRESA = C.EMPRESA AND P.AREA=C.AREA
where p.mes = '1'
and p.ano = '2009'
and P.empresa = 'emp1'
GROUP BY P.empresa, P.AREA


Saludos, cuentanos cómo te queda.

[mjjj]

Esto no funciona ni por mas empeño que le ponga...


select p.area ,
sum(P.monto) AS MONTO,
sum(case when estado = 'F' then monto_compr else 0 end) AS COMPRA,
count(case when estado = 'P' then 1 else 0 end) AS EXCEDIDA
from presupuesto p inner join compras c on c.empresa=c.empresa and c.area = p.area and c.ano = p.ano
and p.mes = c.mes AND P.EMPRESA = C.EMPRESA AND P.AREA=C.AREA and p.oficina = c.oficina
where p.mes = '1'
and p.ano = '2009'
and P.empresa = 'emp1'
and p.oficina = '1'
GROUP BY P.AREA


Lo unico que concuerda son las distintas area que tengo, pero los montos y la cantidad estan disparados.

A mi entender es que al preguntar a 2 tablas, esta se multiplican, lo que conlleva a un enorme numero de registros. Entonces al hacer una consulta sobre estas tablas, las sumas y cantidad son mayores.

Ahora bien, eso creo que es lo que ocurre, pero no se como solucionarlo para obtener lo que ando buscando.

Corrijanme si estoi equivocando.

Gracias.

[fjcg02]

Qué motor de BBDD usas ?

[mjjj]

Firebird 2.0

Saludos

[fjcg02]

select p.area ,
sum(P.monto) AS MONTO,
sum(case when estado = 'F' then monto_compr else 0 end) AS COMPRA,
count(case when estado = 'P' then 1 else 0 end) AS EXCEDIDA
from presupuesto p inner join compras c on c.empresa=c.empresa and c.area = p.area and c.ano = p.ano
and p.mes = c.mes AND P.EMPRESA = C.EMPRESA AND P.AREA=C.AREA and p.oficina = c.oficina
where p.mes = '1'
and p.ano = '2009'
and P.empresa = 'emp1'
and p.oficina = '1'
GROUP BY P.AREA

Bueno,
examina lo que te he marcado en negro. Tiene pinta de que la condicion de la empresa está mal, y por otro lado la oficina no pinta nada en esta guerra - al menos con las tablas de prueba que has aportado ;) -.

Por otro lado, haz lo que te comentaba en un post anterior.
Quita el group by y las funciones de agregado SUM y COUNT. También los CASE. Una vez hecho esto, ¿ la consulta devuelve los registros que debe devolver para sumar y contar lo que debe ? Si no es así, está mal desde el origen. Revisala, y cuando devuelva los registros que tiene que devolver, vuelves a poner las funciones de agregado y el group by.

Ya nos dirás.
Por otro lado, tendrás alguna herramienta para ejecutar la query sin recompilar el programa no ? Me refiero la ibexpert o similar.

Además, no estaría de más que busques información sobre la normalización de las tablas, ya que la estructura que pones no es muy ortodoxa - no lo digo yo, lo dice la teoría de las buenas prácticas - ;).

Ya nos contarás.

Saludos

[Bpascal]

Hola Amigo,

disculpa la tardanza y disculpa que no fui explicitamente claro con el INNER JOIN, inlcluso no te dije toda la historia, la cual es necesario que la domines de lo contrario no podras hacer querys eficientes.

Por favor lee detalladamente las explicacion a continuacion del JOIN y al final del post te escribo el querry que ocupas.

Primero debes saber que hay dos sabores de JOIN:

INNER JOIN
LEFT OUTER JOIN

si tenemos tabla_A:
Id, englishword
--------------
1 bird
2 house
3 dog
5 cat
6 black

si tenemos tabla_B:
Id, spanishword
--------------
1 pajaro
2 casa
3 perro
5 gato

si hacemos el siguiente select:

SELECT
a.id,a.englishword,b.spanishword
FROM
TABLA_A a
INNER JOIN TABLA_B b ON a.id = b.id


el resultado seria:

1 bird pajaro
2 house casa
3 dog perro
5 cat gato


Como vez, el ultimo registro de la tabla_A con id = 6 no aparece en la lista, esto se deve a que INNER JOIN se usa para seleccionar solo los registros que coincidan en el campo relacionado. Como el ID=6 no esta en la tabla_B entonces ese registro no sale. El inner join es mas utilizalo para enlazar tablas de transacciones con su maestro, o tablas detalle con encabezado.

Ahora, si queremos sacar TODOS los registros de la tabla_A con su respectivo dato de la tabla_B pero tambien incluyendo los que no pudieran tener equivalencia en tabla_B entonces harias:


SELECT
a.id,a.englishword,b.spanishword
FROM
TABLA_A a
LEFT OUTER JOIN TABLA_B b ON a.id = b.id



el resultado seria:

1 bird pajaro
2 house casa
3 dog perro
5 cat gato
6.black NULL


Los registros que no tengan su correspondiente equivalencia en tabla_B aparece con valor NULL.

Hasta aqui ya te estaras imaginando que lo que necesitas es usar LEFT OUTER JOIN, porque basicamente tu quieres repasar todos los registros de la tabla presupuesto la par con resumenes si (si los hubiera) de la tabla compras.

Sin envargo en estos ejemplo la relacion es de 1 a 1, o almenos de muchos a 1 es decir, por un registro en la tabla A se espera un tan solo registro en la tabla B, pero el asunto se vuleve diferente cuando la relacion es uno a muchos, por ejemplo:

si la tabla B tuviera:
Id, spanishword
--------------
1 pajaro
1 ave
1 plumifero
2 casa
3 perro
5 gato

como vez, el id=1 esta 3 veces

si se aplica el utimo querry el resultado seria:


1 bird pajaro
1 bird ave
1 bird plumifero
2 house casa
3 dog perro
5 cat gato
6.black NULL


Entonces tu ves que el dato "bird" de la tabla_A se REPITE 3 veces, una vez por cada registro con el ID=1 encontrado en la tabla B.

Por consiguinete, si tu haces el siguinete querry:

select
p.area , sum(P.monto) , sum(case when estado = 'F' then monto_compr else 0 end),
count(case when estado = 'P' then monto_compr else 0 end)
from presupuesto p
LEFT OUTER JOIN compras c on c.area = p.area and c.ano = p.ano
and p.mes = c.mes AND P.EMPRESA = C.EMPRESA

where p.mes = '2009'
and p.ano = '1'
and P.empresa = 'emp'
AND P.oficina = '1'
AND C.OFICINA = 'SANTIAGO'
GROUP BY AREA

Los sum() y los count() de la tabla COMPRAS saldran bien, pero los de la tabla presupuesto saldran mal ya que ahora hay datos duplicados por cada ocurrencia encontrada en compras, de la misma manera como se duplica el dato "Bird" en el ejemplo que te puse tambien aqui el dato p.monto saldra duplicado.

Entonces como se resuleve este tipo de problemas? bueno se resuelve resumiendo la tabla compras UNA TAN SOLA VEZ y enlazando el resultado a la tabla presupuesto usando LEFT OUTER JOIN,

Es decir envez de escribir el nombre de la tabla despues del left outer join se puede perfectamente escribir un SELECT que resume ya la tabla antes de hacer el enlazado, ejemplo:


from presupuesto p LEFT OUTER JOIN

(SELECT
area,ano,mes,empresa,oficina,sum(Monto_compr) as T_monto_compr, count(mont_compr) as C_mon_compr
FROM
Compras
group by area,ano,mes,empresa,oficina)

C ON p.area = c.area and p.ano = c.ano and p.mes = c.mes AND P.EMPRESA = C.EMPRESA



Esto nos dara una especie de tabla temporal ya resumida por lo que queramos y poder ser enlazada con otra tabla maestra por ejemplo usando inner o outer join segun sea la necesidad.

Ya terminamos?, no todavia, :rolleyes:, c.Tot_compr y c.C_mon_compr ya tienen el total de la compra y la cuenta de la compra lista para enlazarla con una tabla mestra, pero tu todavia necesitas resumir la tabla presupuesto para sumar y contar el valor monto.

La manera de sumarizar la tabla presupuesto e incluir los totales de la tabla compra sin duplicar se puede hacer usando el comando DISTINCT.

creo que este es el querry final:



SELECT
p.area , sum(P.monto) , sum(distintc c.tot_compr), sum(distintc c.C_mon_compr)
FROM
presupuesto p
LEFT OUTER JOIN

(SELECT
area,ano,mes,empresa,oficina,sum(Monto_compr) as T_monto_compr, count(mont_compr) as C_mon_compr
FROM
Compras
group by area,ano,mes,empresa,oficina)

C ON p.area = c.area and p.ano = c.ano and p.mes = c.mes AND P.EMPRESA = C.EMPRESA

where p.mes = '2009'
and p.ano = '1'
and P.empresa = 'emp'
AND P.oficina = '1'
AND C.OFICINA = 'SANTIAGO'
GROUP BY AREA


Favor notar que en el select de la tabla compras la estoy resumiendo sin tomar en considerancion el campo estado.


Espero que te funcione o por lo menos te haya dado la suficiente informacion para adaptar tus querrys.

[AzidRain]

Haz dado en el clavo BPascal, lo que el amigo necesitaba es una explicación así de clara de lo joins...mis respetos señor.

Estaba analizando el caso y no se si me equivoque pero al parecer nuestro amigo atacó el problema con lo que él ya sabía sin investigar un poquito más a ver que otras cosas se pueden hacer. A todos nos pasó en su momento y más de uno regresamos a nuestros propios códigos a "optimizarlos" .

[mjjj]

Muchas gracias por tu tan clara explicación, pero ocurre algo, al tratar de hacer la consulta en el IBExpert, me arroja un error en la primera linea, No reconoce el campo "C.T_monto_compr", que es el campo de la sumatoria de la tabla compras "sum(Monto_compr) as T_monto_compr".

Al parecer no reconoce este campo ya que es la respuesta de la segunda consulta.

porque ocurrira esto?
utilizo Firebird 2.0, sera que en esta version no se puede realizar este tipo de consultas?

y lo ultimo, donde agrego la considion del campo estado de la tabla compras?
ya que son 2 condiciones distintas, 'F' para la suma y 'P' para el count.

Les dejo mis dudas, y de ante mano muchas gracias por todo, me han ayuda muchisimo.

Saludos

[fjcg02]

Eh! Bpascal, estupendísima explicación. Además eres el único que has dado en el clavo.
Yo creo que hemos aprendido todos algo con tu aportación.

Saludos

[Bpascal]

Muchas gracias por tu tan clara explicación, pero ocurre algo, al tratar de hacer la consulta en el IBExpert, me arroja un error en la primera linea, No reconoce el campo "C.T_monto_compr", que es el campo de la sumatoria de la tabla compras "sum(Monto_compr) as T_monto_compr".


Revisa bien si es un error de deletreo de nombres, espero que eso sea, el codigo lo probé en MsSql 2005, asegurate que el SELECT donde se resume la tabla de compras esta entre parentesis y que el alias "C" esta DESPUES de cerrar parentesis.


y lo ultimo, donde agrego la considion del campo estado de la tabla compras?
ya que son 2 condiciones distintas, 'F' para la suma y 'P' para el count.


En el SELECT donde se resume la tabla compras, toma nota que es muy similar a tu codigo original la diferencia es que el select anidado que utilizabas se movió del select principal y aqui no lleva condiciones.

[mjjj]

Bpascal, lo he verificado varias veces, y no es un problema de deletreo, incluso probe con otro ejemplo utilizando la misma idea y tampoco funciona.

Al parecer Firebird 2.0, no soporta este tipo de consultas.

Podrá ser esto??

[fjcg02]

Pero esto sí debe de 'tragar'

select p.area , P.monto,
sum(case when estado = 'F' then monto_compr else 0 end) AS COMPRA,
count(case when estado = 'P' then 1 else 0 end) AS EXCEDIDA
from presupuesto p inner join compras c on p.empresa=c.empresa and c.area = p.area and c.ano = p.ano
and p.mes = c.mes AND P.EMPRESA = C.EMPRESA AND P.AREA=C.AREA and p.oficina = c.oficina
where p.mes = '1'
and p.ano = '2009'
and P.empresa = 'emp1'
and p.oficina = '1'
GROUP BY P.AREA, P.MONTO

Date cuenta de que he quitado la función de agregado al campo monto y lo he añadido al group by.

Ya nos contarás.

Saludos

[mjjj]

Amigos, no me funciona para nada. La idea que mas me gusta fue la de Bpascal, pero tampoco resulto, revise que estuviera todo bien escrito, y asi es.

Será que utilizo Firebird 2.0, que no permite este tipo de consultas.

Si es asi que me recomiendan hacer?

Gracias

[mjjj]

Retomando este tema que me tiene bastante afligido, voy a plantear otra pregunta, que de poder encontrar una solucion, se me facilitaria mi problema general.

Presento la consulta SQL que requiero, pero que me arroja un error.


select INGRESO - EGRESO, sum(case when tipo = 'I' THEN CANTIDAD ELSE 0 END) as INGRESO,
sum(case when tipo = 'E' THEN CANTIDAD ELSE 0 END) as EGRESO
from INVENTARIO WHERE EMPRESA = 'EMP1'


Lo que ocurre es que no puedo usar los campos calculos para volver a realizar otrso calculos con ellos.

Como puedo hacer esta consulta de otra manera, lo mas eficiente posible?

Gracias

[AzidRain]

select INGRESO - EGRESO, sum( if(tipo = 'I', CANTIDAD,0)) as INGRESO,
sum( if(tipo= 'E', CANTIDAD, 0) ) as EGRESO
from INVENTARIO WHERE EMPRESA = 'EMP1'


No se si la sintaxis de IF sea la misma en FB, pero checale.

[mjjj]

Hola pude solucionar mi problema con el siguiente codigo


select i.cod_insum,i.nom_insum, i.unidad, i.CRITICO, i.rotacion,
(sum( iif(M.tipo = 'I', CANTIDAD,0))) as INGRESO,
(sum( iif(M.tipo= 'E', CANTIDAD,0))) as EGRESO,
((sum( iif(M.tipo = 'I', CANTIDAD,0))) - (sum( iif(M.tipo = 'E', CANTIDAD,0)))) AS SALDO,
((sum( iif(M.tipo = 'I', PRECIO*CANTIDAD,0))) - (sum( iif(M.tipo = 'E', PRECIO*CANTIDAD,0))))/
(IIF((sum( iif(M.tipo = 'I', CANTIDAD,0))) - (sum( iif(M.tipo = 'E', CANTIDAD,0)))=0,1,(
(sum( iif(M.tipo = 'I', CANTIDAD,0))) - (sum( iif(M.tipo = 'E', CANTIDAD,0)))))) AS UNIT_POND,
(((sum( iif(M.tipo = 'I', PRECIO*CANTIDAD,0))) - (sum( iif(M.tipo = 'E', PRECIO*CANTIDAD,0))))/
(IIF((sum( iif(M.tipo = 'I', CANTIDAD,0))) - (sum( iif(M.tipo = 'E', CANTIDAD,0)))=0,1,(
(sum( iif(M.tipo = 'I', CANTIDAD,0))) - (sum( iif(M.tipo = 'E', CANTIDAD,0)))))))*
(((sum( iif(M.tipo = 'I', CANTIDAD,0))) - (sum( iif(M.tipo = 'E', CANTIDAD,0))))) AS SUBTOTAL

from insumos i INNER join inventario m
ON I.cod_insum = M.ID
WHERE M.EMPRESA = 'emp'
AND I.EMPRESA = 'emp'
GROUP BY i.cod_insum,i.nom_insum, i.unidad, i.CRITICO, i.rotacion


Funciona perfecto e instantaneo, el unico problema es que solo me muestra los insumos que tengan algun tipo de movimiento, es decir, que exitan algun registro de ese insumo en particular en la tabla invetario.

La tabla insumos contiene la informacion de todos los insumos existentes, y la tabla inventario todos los movimientos de los insumos (tipo = 'I' si es un ingreso, y tipo = 'E' si es un egreso)

El asunto es que si no hay ningun movimiento (no hay registro de algun insumo en particular en la tabla inventario), no aparece en mi consulta.

Como puedo hacer la consulta para que arroje todos los insumos, incluso aquellos que no tienen moviemiento.

Espero que se haya entendido.

Saludos, y gracias

[fjcg02]

Tendrás que utilizar LEFT JOIN en lugar de INNER JOIN.
No sé cual es la tabla que tienes que poner a cada lado, pero tienes el 50% por cierto de posibilidades de acertar a la primera.

Saludos