--- In firebird-support@yahoogroups.com, Thomas Steinmaurer <ts@...> wrote:

>
> Hello Karol,
>
> > 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(EXTRACT(YEAR FROM DATEADD(1 MONTH TO DM.DATA_DOC)) || '-' || EXTRACT(MONTH FROM DATEADD(1 MONTH TO DM.DATA_DOC)) || '-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 got error
> >
> > SQL Message : -104
> > Invalid token
> >
> > Engine Code : 335544569
> > Engine Message :
> > Dynamic SQL Error
> > SQL error code = -104
> > Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
> >
> > ---------------------------------------------
> > is this FB problem or i doing some wrong??
> >
> > i try tis "problematic" expression against simple select and work ok - what is wrong?
> > SELECT
> > CAST(EXTRACT(YEAR FROM DATEADD(1 MONTH TO DM.DATA_DOC)) || '-' || EXTRACT(MONTH FROM DATEADD(1 MONTH TO DM.DATA_DOC)) || '-01' AS DATE)
> > FROM
> > DOC_MAG DM
>
> I think your problem isn't related to the place where you think it is. I
> rather think that COALESCE (...) isn't recognized as aggregate function,
> thus the error in respect to a mismatch of the select list and the group
> by list.
>
>
>
> --
> With regards,
>
> Thomas Steinmaurer
> Upscene Productions
> http://www.upscene.com
> http://blog.upscene.com/thomas/
>
> Download LogManager Series, FB TraceManager today!
> Continuous Database Monitoring Solutions supporting
> Firebird, InterBase, Advantage Database, MS SQL Server
> and NexusDB!
>

this is not related to coalesce
because below query work ok and i only put explicit date string like '2011-01-01' in cast and also if i remove coalesce at all i got the same error

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('2011-01-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