Subject Re: select with subselects in columns or in joins? (also MERGE n SORT in plans)
Author Svein Erling
Hi!

I've no idea why things are duplicated in your first plan, and I don't think I like your second query due to it not yielding any result if any of the parts should return NULL (that is, the parts the first query covers for using COALESCE). However, I do have a third suggestion for you that I think looks simpler:

with tempspt(codproduto, sumspt) as (
select SPt.CODPRODUTO, SUM (spt.qtde)
FROM SAIDAPRODUTOS SPt
INNER JOIN SAIDANOTAS sntt ON SPt.CODSAIDANOTA = sntt.CODSAIDANOTA
WHERE sntt.DATA >= :DATA_INI
AND sntt.PEDIDO = 0
GROUP BY 1),

tempept(codproduto, sumept) as (
select ePt.CODPRODUTO, SUM (ept.qtde)
FROM entradaprodutos EPt
inner join entradanotas ett on EPT.codentradanota = ett.codentradanota
WHERE ett.DATA >= :DATA_INI
GROUP BY 1),

tempdvp(codproduto, sumdvp) as (
select dvp.CODPRODUTO, sum(dvp.qtde)
from devprodutos dvp
where dvp.data >= :DATA_INI
GROUP BY 1)

select
p.CODPRODUTO,
p.DESCRICAO AS DESCRICAO,
(p.ESTOQUE + coalesce(tspt.sumspt, 0)
- coalesce(tept.sumept, 0)
- coalesce(tdvp.sumdvp, 0)
from PRODUTOS P
left join tempspt tspt on p.codproduto = tspt.codproduto
left join tempept tept on p.codproduto = tept.codproduto
left join tempdvp tdvp on p.codproduto = tdvp.codproduto

Unfortunately, I have no idea about performance, WITH is a fairly new construct in Firebird, and I still spend most of my Firebirdy time with Firebird 1.5...

HTH,
Set