--- In firebird-support@yahoogroups.com, "Muthu Annamalai"
<pearlamerica@...> wrote:

>
> --- In firebird-support@yahoogroups.com, "mspencewasunavailable"
> <firebird@> wrote:
> >
> > --- In firebird-support@yahoogroups.com, "Muthu Annamalai"
> > <pearlamerica@> wrote:
> > >
> > > I need help with arranging months in my stored procedure. See

my

> SP
> > >
> > > FOR SELECT FIRST 12 EXTRACT(MONTH FROM RECEIPTDATE),COUNT

(*),SUM

> > > (AMOUNT)
> > > FROM RECEIPT
> > > GROUP BY 1
> > > INTO :PERIOD,:RECEIPTS,:SALES
> > > DO SUSPEND;
> > >
> > > The stored procedure results are like this
> > >
> > > PERIOD
> > > 1
> > > 2
> > > 8
> > > 9
> > > 10
> > > 11
> > > 12
> > >
> > > I need to sort year wise first and then month as follows
> > >
> > > PERIOD
> > >
> > > 10
> > > 11
> > > 12
> > > 1
> > > 2
> > > 8
> > > 9
> > >
> > > Any help is highly appreciated
> > >
> > > Thanks,
> > >
> > > Muthu
> > >
> >
> > Haven't tried either of these, but:
> >
> > ORDER BY (CASE WHEN PERIOD >= 10 THEN PERIOD - 12 ELSE PERIOD

END)

> >
> > should work if you always want to start with October (because,

for

> > example, that's the beginning of your fiscal year).
> >
> > More generally, if you just want to get the next twelve months

and

> > have an arbitrary start date, you could do this:
> >
> > ORDER BY EXTRACT(YEAR FROM RECEIPTDATE) * 100 + EXTRACT(MONTH

FROM

> > RECEIPTDATE)
> >
> > Michael D. Spence
> > Mockingbird Data Systems, Inc.
> >
>
> I can't use your first solution as the period doesn't always

starts

> with oct, as endusers might have different fiscal year.
>
> The Second solution, order by gives me the following error
>
> "Invalid expression in the ORDER BY clause (not contained in

either

> an aggregate function or the GROUP BY clause)."
>
> Thanks for your effort.
>
> Muthu
>

It just wants you to add the missing expression to the GROUP BY
clause:

FOR SELECT FIRST 12 EXTRACT(MONTH FROM RECEIPTDATE) as PERIOD,COUNT
(*),SUM (AMOUNT)
FROM RECEIPT GROUP BY PERIOD, EXTRACT(YEAR FROM RECEIPTDATE) * 100 +
EXTRACT(MONTH FROM RECEIPTDATE)
ORDER BY EXTRACT(YEAR FROM RECEIPTDATE) * 100 + EXTRACT(MONTH FROM
RECEIPTDATE)