Subject Re: [firebird-support] Query and problem with nulls
Author setysvar
>LEFT JOIN DOC_TESTA DT ON D.ID = DT.DEPOSITO_ID, DOC_CORPO DC

Ouch, Luigi, I don't think I've ever seen anyone using an implicit join
after an explicit left join like this before! I have no clue how
Firebird handles this (and I'm uncertain if I would want to know it or
not). Generally, you should always use explicit JOINs, implicit JOINs
are historical remnants that you could have eliminated when InterBase
(the ancestor of Firebird) started supporting the explicit JOINs of
SQL-92 (don't know when InterBase started doing that, but I would assume
it was between 1992 and 1999).

Since you don't seem to have any problems with treating parameters as if
they were variables, I expect the following query to give you the result
you want:

SELECT DT.DEPOSITO_ID,
       SUM(IIF(DT.DATA < :DaData, DC.CARICO - DC.SCARICO, 0)) RIPORTO,
       SUM(IIF(DT.DATA BETWEEN :DaData AND :AData, DC.CARICO,    0))
CARICO,
       SUM(IIF(DT.DATA BETWEEN :DaData AND :AData, DC.SCARICO,   0))
SCARICO,
       SUM(IIF(DT.DATA <= :AData, DC.CARICO - DC.SCARICO, 0)) ESISTENZA,
       SUM(IIF(DT.DATA BETWEEN :DaData AND :AData, DC.IMPEGNATI, 0))
IMPEGNATI,
       SUM(IIF(DT.DATA BETWEEN :DaData AND :AData, DC.ORDINATI,  0))
ORDINATI,
FROM DOC_CORPO DC
JOIN DOC_TESTA DT ON DC.DOC_TESTA_ID = DT.ID
WHERE DC.ARTICOLO_ID = :ID
GROUP BY DT.DEPOSITO_ID

You have said that you want rows returned if there are older data, but
not between :DaData and :AData in DOC_TESTA, you haven't specified
whether you want any rows returned if there are only newer data than
:AData in DOC_TESTA. My query will return a row (albeit with lots of
0.0...) even if you specify ancient values, e.g. 01/01/1935 in :DaData
and 31/12/1935 in :AData (as long as there are any rows with the correct
ARTICOLO_ID in the table).

HTH,
Set