One example of its Power
This SQL runs in just 400 milliseconds with a database
of 10000 articles, as you can see there are several nested sql referenced.
Server:
4 Gb ram, Intel Core 2 Quad Q6600 a 2.4 GHz, HD Sata 2.
Select ID_PEDIDO_PROVEEDOR,FECHA_ENTRADA,COSTE,id_articulo,articulo,id_almacen,almacen,entradas,salidas,stock,reservado,(stock - Reservado) as DISPONIBLE from ( Select ID_PEDIDO_PROVEEDOR, FECHA_ENTRADA, COSTE, ID_ARTICULO, (Select Descripcion from MAN_ARTICULOS_DEFINIDOS Where id_articulo = id) as Articulo, ID_almacen, (SELECT DENOMINACION FROM MAN_ALMACENES WHERE ID=ID_ALMACEN) AS ALMACEN, sum(ENTRADAS) AS ENTRADAS, sum(SALIDAS) AS SALIDAS, sum((ENTRADAS-SALIDAS)) AS STOCK, (CASE WHEN Sum(RESERVADO) IS NULL THEN 0 ELSE Sum(RESERVADO) END) as Reservado from ( Select ID_PEDIDO_PROVEEDOR, FECHA_ENTRADA, COSTE, ID_ARTICULO, ARTICULO, ID_ALMACEN, SUM(CANTIDAD) AS ENTRADAS, /* *********************************** CAMPO CALCULADO RESERVADO */ (Select Sum(Stock) AS RESERVADO from (/* SQL RAIZ */ Select ID_ARTICULO,id_cab_albaran_entrada, (Select id_almacen from Cab_albaranes_entrada where id = id_cab_albaran_entrada) as ID_ALMACEN, (CODIGO_ARTICULO || ' - ' || DESCRIPCION) AS articulo, sum(CANTIDAD) AS STOCK from LIN_ALBARANES_ENTRADA WHERE (id_articulo = raiz.id_Articulo) GROUP BY ID_ARTICULO,id_cab_albaran_entrada,(CODIGO_ARTICULO || ' - ' || DESCRIPCION) ) /* FIN - SQL RAIZ */ Group by ID_ARTICULO,ARTICULO) as RESERVADO, /* ************************************ FIN CAMPO CALCULADO RESERVADO */ /* ************************************ CAMPO CALCULADO SALIDAS */ ( Select coalesce (Sum(Stock),0 ) AS SALIDAS from ( /* SQL RAIZ 2 */ select ID_ARTICULO, id_cab_albaran_SALIDA, ID_ALMACEN_Destino, (CODIGO_ARTICULO || ' - ' || DESCRIPCION) AS articulo, sum(CANTIDAD) AS STOCK from LIN_ALBARANES_SALIDA Where (id_articulo = raiz.id_Articulo) GROUP BY ID_ARTICULO,id_cab_albaran_salida,id_almacen_destino,(CODIGO_ARTICULO || ' - ' || DESCRIPCION) ) ) /* *************************************FIN CAMPO CALCULADO SALIDAS 50 */ from ( /* SQL RAIZ */ select ID_PEDIDO_PROVEEDOR, FECHA_ENTRADA, COSTE, ID_ARTICULO, id_cab_albaran_entrada, CANTIDAD, (Select id_almacen from Cab_albaranes_entrada where id = id_cab_albaran_entrada) as ID_ALMACEN, (CODIGO_ARTICULO || ' - ' || DESCRIPCION) AS articulo from LIN_ALBARANES_ENTRADA AS raiz GROUP BY ID_ALMACEN, ID_ARTICULO, (CODIGO_ARTICULO || ' - ' || DESCRIPCION), ID_PEDIDO_PROVEEDOR, FECHA_ENTRADA, COSTE, id_cab_albaran_entrada, cantidad ) AS RAIZ GROUP BY ID_ALMACEN, ID_ARTICULO,ARTICULO, ID_PEDIDO_PROVEEDOR, FECHA_ENTRADA, COSTE) AS STOCK_TOTAL WHERE (ID_ALMACEN = :ALMACEN) OR (:ALMACEN = 0) GROUP BY id_ALMACEN,ALMACEN, ID_ARTICULO,ARTICULO, ID_PEDIDO_PROVEEDOR, FECHA_ENTRADA, COSTE ORDER BY ARTICULO,FECHA_ENTRADA,COSTE ASC )