--- In firebird-support@yahoogroups.com, "karolbieniaszewski" <liviuslivius@...> wrote:

>
>
> --- 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
>

too many hour of work for months ..

i find where the error is
when i put this on fb tracker have mind blink ;-)
and i see now that i try to sum from DATE_DOC field which not exists as result of grouping query

error message text is also ok

Karol Bieniaszewski