I would suggest replacing your query with something like: with tmp(DaData) as (select cast(:DaData as date) from rdb$database) SELECT DT.DEPOSITO_ID, SUM(iif(DT.DATA < t.DaData, DC.CARICO - DC.SCARICO, 0)) AS RIPORTO, SUM(iif(DT.DATA >= t.DaData, DC.CARICO, 0)) as Carico, SUM(iif(DT.DATA >= t.DaData, DC.SCARICO, 0)) as Scarico, SUM(iif(DT.DATA >= t.DaData, DC.CARICO - DC.SCARICO, 0)) as ESISTENZA, SUM(iif(DT.DATA >= t.DaData, DC.IMPEGNATI, 0)) as Impegnati, SUM(iif(DT.DATA >= t.DaData, DC.ORDINATI, 0)) as Ordinati FROM DOC_CORPO DC JOIN DOC_TESTA DT ON DC.DOC_TESTA_ID = DT.ID CROSS JOIN tmp T WHERE DC.ARTICOLO_ID = :ID AND DT.DATA <= :AData GROUP BY DT.DEPOSITO_ID
The reason for the CTE is because parameters are not variables and I have experienced cases when I've tried to refer to the same parameter twice only to discover that IBO treated it as two separate parameters (I don't know what components you use, but the CTE eliminates any ambiguity). In addition to this, I updated your query from implicit to explicit JOIN and simplified a bit (well, at least in my opinion). The way I would expect my query to differ from yours, is that mine will return rows with values in RIPORTO, but only 0's for the other summed fields. I've no clue whether or not this is what you want, please tell us more specifically what you're looking for if it is something different. HTH, Set 2018-01-04 13:32 GMT+01:00 Mark Rotteveel [email protected] [firebird-support] <[email protected]>: > Without relevant DDL and sample data we can't really help you. > > Mark > > On 4-1-2018 12:36, Luigi Siciliano [email protected] > [firebird-support] wrote: > > Hallo, > > > > The following query works fine but returns *all nulls* if no rows > > between :DaData and :AData > > > > SELECT > > > > DEPOSITO_ID, > > RIPORTO, > > CARICO, > > SCARICO, > > RIPORTO + ESISTENZA AS ESISTENZA, > > IMPEGNATI, > > ORDINATI > > FROM > > ( > > SELECT > > DT.DEPOSITO_ID, > > (SELECT > > SUM(DC2.CARICO - DC2.SCARICO) > > FROM > > DOC_CORPO DC2, > > DOC_TESTA DT2 > > WHERE > > DC2.DOC_TESTA_ID = DT2.ID > > AND DC2.ARTICOLO_ID = :ID > > AND DT2.DATA < :DaData > > ) AS RIPORTO, > > SUM(DC.CARICO) as Carico, > > SUM(DC.SCARICO) as Scarico, > > SUM(DC.CARICO - DC.SCARICO) as ESISTENZA, > > SUM(DC.IMPEGNATI) as Impegnati, > > SUM(DC.ORDINATI) as Ordinati > > FROM > > DOC_CORPO DC, DOC_TESTA DT > > WHERE > > DC.DOC_TESTA_ID = DT.ID > > AND DC.ARTICOLO_ID = :ID > > AND DT.DATA >= :DaData > > AND DT.DATA <= :AData > > GROUP BY > > DT.DEPOSITO_ID > > ) > > > > How I can modify it? > > > > Thanks. > > > > > -- > Mark Rotteveel > > > ------------------------------------ > Posted by: Mark Rotteveel <[email protected]> > ------------------------------------ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > > Visit http://www.firebirdsql.org and click the Documentation item > on the main (top) menu. Try FAQ and other links from the left-side menu > there. > > Also search the knowledgebases at http://www.ibphoenix.com/ > resources/documents/ > > ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ > ------------------------------------ > > Yahoo Groups Links > > > >
