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

>
>
> --- In firebird-support@yahoogroups.com, "pavle.grebenar" <pavle.grebenar@> wrote:
> >
> >
> >
> > --- In firebird-support@yahoogroups.com, "karolbieniaszewski" <liviuslivius@> wrote:
> > >
> > > 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??
> > >
> >
> > Because of '(' and ')', group by clause is for first select, not for second
> >
>
> group is not explicit for second query but result of group is
> and only available in sub query is EXTRACT(YEAR FROM DM.DATA_DOC) and EXTRACT(MONTH FROM DM.DATA_DOC)
> but i wrongly try to use EXTRACT(YEAR FROM DATEADD(1 MONTH TO DM.DATA_DOC)) what is not result of grouping query
>

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 */
/* IMO it's redudant: DMP.DATA_DOC BELONG TO MONTH (in year) OF DM.DATA_DOC */
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)) -- belong to year of DM.DATA_DOC
AND
(EXTRACT(MONTH FROM DMP.DATA_DOC)=EXTRACT(MONTH FROM DM.DATA_DOC) -- AND belong to month of 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