| Subject | Re: [firebird-support] Query and problem with nulls |
|---|---|
| Author | Svein Erling Tysvær |
| Post date | 2018-01-04T13:17:10Z |
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 mark@... [firebird-support] <firebird-support@yahoogroups.com>:
Without relevant DDL and sample data we can't really help you.
Mark
On 4-1-2018 12:36, Luigi Siciliano luigisic@...
Mark Rotteveel[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.
>
--
------------------------------ ------
Posted by: Mark Rotteveel <mark@...>
------------------------------ ------
++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++ ++++++
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
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/ firebird-support/
<*> Your email settings:
Individual Email | Traditional
<*> To change settings online go to:
http://groups.yahoo.com/group/ firebird-support/join
(Yahoo! ID required)
<*> To change settings via email:
firebird-support-digest@ yahoogroups.com
firebird-support-fullfeatured@ yahoogroups.com
<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@ yahoogroups.com
<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/ us/yahoo/utos/terms/