Hi Lester,

> > Well, MOD isn't an aggregate function like SUM, AVG, ...
> > so the third column in the SELECT list is missing in
> > your GROUP BY. And, it isn't directly related to the
> > MOD function, because the following query gives the same
> > error.
> >
> > SELECT APPLET
> > , INIT_ID AS STAFF
> > FROM TICKET
> > GROUP BY APPLET
>
> OK I was trying to simplify to spot the problem
>
> The full query is
>
> SELECT T.APPLET
> , T.INIT_ID AS STAFF
> , MOD( T.INIT_ID, 1000 ) AS STAFF_ID
> , COUNT (*) AS COUNTS
> , T.INIT_ID
> FROM TICKET T
> WHERE T.TICKET_REF BETWEEN :FROMD AND :TOD
> GROUP BY T.APPLET, T.INIT_ID
> ORDER BY T.APPLET, 2
>
> And is running on a number of sites without any problem with
> Firebird 0.9.4, but gives the error on FB1 Build 821.
> Comment out the MOD line and the query will prepare. Switch
> back to the older Firebird and the query will prepare ( in
> IB_SQL ).
>
> Where do I go next is the question?

Well, IMHO it's a misbehaviour of the 0.9.4 Build, that
your query is allowed. Again, with your query above, there
is one column in your field list of your SELECT statement,
which is missing in the GROUP BY, namely the MOD(...)
expression.

Have you tried?

SELECT T.APPLET
, T.INIT_ID AS STAFF
, MOD( T.INIT_ID, 1000 ) AS STAFF_ID
, COUNT (*) AS COUNTS
, T.INIT_ID
FROM TICKET T
WHERE T.TICKET_REF BETWEEN :FROMD AND :TOD
GROUP BY T.APPLET, T.INIT_ID, MOD( T.INIT_ID, 1000 )
ORDER BY T.APPLET, 2

Anyway, I think it isn't directly related to IBO, so ib-support
might be the proper place for your problem.


Regards,
Thomas Steinmaurer
IB LogManager 2.1 - The Logging/Auditing Tool for InterBase and Firebird
http://www.iblogmanager.com