--- In firebird-support@yahoogroups.com, Svein Erling Tysvær <svein.erling.tysvaer@...> wrote:

>
> You cannot put COALESCE around your sum. There are a few possible solutions, one of them might be:
>
> SELECT
> DM.ID_MAG AS NR_MAG,
> EXTRACT(YEAR FROM DM.DATA_DOC) AS ROK, EXTRACT(MONTH FROM DM.DATA_DOC) AS MIESIAC ,
> SUM(COALESCE(DMP.WART_NETT, 0) AS PRZYCH_MIES
> FROM
> DOC_MAG DM
> LEFT JOIN DOC_MAG DMP
> LEFT JOIN MAG_TYP_DOC MTD2
> ON DMP.TYP_DOC=MTD2.ID
> AND DMP.ID_MAG=DM.ID_MAG
> AND MTD2.TYP=1
> AND DMP.STATUS=1
> AND DMP.DATA_DOC>=
> CAST(EXTRACT(YEAR FROM DM.DATA_DOC) || '-' || EXTRACT(MONTH FROM DM.DATA_DOC) || '-01' AS DATE)
> AND DMP.DATA_DOC<CAST(EXTRACT(YEAR FROM DATEADD(1 MONTH TO DM.DATA_DOC)) || '-' || EXTRACT(MONTH FROM DATEADD(1 MONTH TO DM.DATA_DOC)) || '-01' AS DATE)
> AND EXTRACT(YEAR FROM DMP.DATA_DOC)=EXTRACT(YEAR FROM DM.DATA_DOC)
> AND EXTRACT(MONTH FROM DMP.DATA_DOC)=EXTRACT(MONTH FROM DM.DATA_DOC)
> WHERE
> DM.ID_MAG=23
> GROUP BY DM.ID_MAG, EXTRACT(YEAR FROM DM.DATA_DOC), EXTRACT(MONTH FROM DM.DATA_DOC)
> ORDER BY DM.ID_MAG, 2 DESC, 3 DESC
>
> HTH,
> Set
>

Why do you think that i cannot put coalesce aroud my "sum" this is sum from select not select coalesce(sum ..

and also i try without coalesce and i got the same error
if i change query to:

SELECT
DM.ID_MAG AS NR_MAG,
EXTRACT(YEAR FROM DM.DATA_DOC) AS ROK, EXTRACT(MONTH FROM DM.DATA_DOC) AS
MIESIAC ,
COALESCE((SELECT SUM(DMP.WART_NETT)
FROM
DOC_MAG DMP
INNER JOIN MAG_TYP_DOC MTD2 ON DMP.TYP_DOC=MTD2.ID
WHERE
(DMP.ID_MAG=DM.ID_MAG)
AND (MTD2.TYP=1)
AND (DMP.STATUS=1)
/* here are problem */
AND (DMP.DATA_DOC>=
CAST(EXTRACT(YEAR FROM DM.DATA_DOC) || '-' || EXTRACT(MONTH FROM
DM.DATA_DOC) || '-01' AS DATE)
AND
DMP.DATA_DOC<CAST('2011-02-01' AS DATE)
)
/* end of problem */
AND ( ((EXTRACT(YEAR FROM DMP.DATA_DOC)=EXTRACT(YEAR FROM
DM.DATA_DOC)) AND (EXTRACT(MONTH FROM DMP.DATA_DOC)=EXTRACT(MONTH FROM
DM.DATA_DOC))) )
),0 ) AS PRZYCH_MIES
FROM
DOC_MAG DM
WHERE
(DM.ID_MAG=23)
GROUP BY DM.ID_MAG, EXTRACT(YEAR FROM DM.DATA_DOC), EXTRACT(MONTH FROM
DM.DATA_DOC)
ORDER BY DM.ID_MAG, 2 DESC, 3 DESC


i put explicit date string '2011-02-01' and query work ..

Karol Bieniaszewski