| Subject | RE: [firebird-support] How merge two queries |
|---|---|
| Author | Edward Mendez |
| Post date | 2014-09-10T16:24:48Z |
Luigi,
Give this a try.
Select ANNO, MAX(IMPONIBILE), MAX(IMPOSTA) FROM (
select
EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) AS ANNO,
SUM(DC.IMPORTO) AS IMPONIBILE,
0 as IMPOSTA
from
DOC_CORPO DC,
DOC_TESTA DT,
VOCI V
WHERE
DT.ID = DOC_TESTA_ID
AND DT.DOCUMENTO_ID <> 'PRO'
AND EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) = 2013
AND DC.VOCE_ID = V.ID
AND V.CONTRIBUTI = 1
GROUP BY EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) UNION ALL
select
EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) AS ANNO,
0 as IMPONIBILE,
SUM(DC.IMPORTO) AS IMPOSTA
from
DOC_CORPO DC,
DOC_TESTA DT
WHERE
DT.ID = DOC_TESTA_ID
AND DT.DOCUMENTO_ID <> 'PRO'
AND EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) = 2013
AND DC.VOCE_ID = 'ENPACL'
GROUP BY EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) ) T1
GROUP BY ANNO;
Thanks,
Ed Mendez
Give this a try.
Select ANNO, MAX(IMPONIBILE), MAX(IMPOSTA) FROM (
select
EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) AS ANNO,
SUM(DC.IMPORTO) AS IMPONIBILE,
0 as IMPOSTA
from
DOC_CORPO DC,
DOC_TESTA DT,
VOCI V
WHERE
DT.ID = DOC_TESTA_ID
AND DT.DOCUMENTO_ID <> 'PRO'
AND EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) = 2013
AND DC.VOCE_ID = V.ID
AND V.CONTRIBUTI = 1
GROUP BY EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) UNION ALL
select
EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) AS ANNO,
0 as IMPONIBILE,
SUM(DC.IMPORTO) AS IMPOSTA
from
DOC_CORPO DC,
DOC_TESTA DT
WHERE
DT.ID = DOC_TESTA_ID
AND DT.DOCUMENTO_ID <> 'PRO'
AND EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) = 2013
AND DC.VOCE_ID = 'ENPACL'
GROUP BY EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) ) T1
GROUP BY ANNO;
Thanks,
Ed Mendez
> -----Original Message-----main
> From: firebird-support@yahoogroups.com [mailto:firebird-
> support@yahoogroups.com]
> Sent: Wednesday, September 10, 2014 12:02 PM
> To: firebird-support@yahoogroups.com
> Subject: [firebird-support] How merge two queries
>
> Hallo,
> I have two queries:
>
> First (results: ANNO, IMPONIBILE):
> select
> EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) AS ANNO,
> SUM(DC.IMPORTO) AS IMPONIBILE
> from
> DOC_CORPO DC,
> DOC_TESTA DT,
> VOCI V
> WHERE
> DT.ID = DOC_TESTA_ID
> AND DT.DOCUMENTO_ID <> 'PRO'
> AND EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) = 2013
> AND DC.VOCE_ID = V.ID
> AND V.CONTRIBUTI = 1
> GROUP BY EXTRACT(YEAR FROM DT.DATA_DOCUMENTO)
>
>
> Second (results: ANNO, IMPOSTA):
> select
> EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) AS ANNO,
> SUM(DC.IMPORTO) AS IMPOSTA
> from
> DOC_CORPO DC,
> DOC_TESTA DT
> WHERE
> DT.ID = DOC_TESTA_ID
> AND DT.DOCUMENTO_ID <> 'PRO'
> AND EXTRACT(YEAR FROM DT.DATA_DOCUMENTO) = 2013
> AND DC.VOCE_ID = 'ENPACL'
> GROUP BY EXTRACT(YEAR FROM DT.DATA_DOCUMENTO)
>
> There is a way to merge those two queries to obtain the same result in one
> query that results: ANNO, IMPONIBILE, IMPOSTA?
>
> Thanks.
>
> --
>
> Luigi Siciliano
> --------------------------
>
>
>
> ------------------------------------
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> ++
>
> Visit http://www.firebirdsql.org and click the Documentation item on the
> (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
>
>
>