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!