Hi Lester,

> I'll start here because it's IB_SQL I am seeing the problem
> in, but it's probably a Firebird niggle.

Niggling? I have a deja vu ;-))

> Have an older program that uses a UDF to do a MOD on a
> number, and return the lower three numbers of a four number
> staff ID. ( Fourth number is staff team )
>
> Have switched to the .821 relase of Firebird for most
> things, but these sites have still got 0.9.4 release.
> When I try to use the MOD UDF in .821 build, I am getting an
> 'invalid column reference' error under some conditions.
>
> SELECT APPLET
> , INIT_ID AS STAFF
> , MOD( INIT_ID, 1000 ) AS STAFF_ID
> FROM TICKET
>
> Works Fine
>
> SELECT APPLET
> , INIT_ID AS STAFF
> , MOD( INIT_ID, 1000 ) AS STAFF_ID
> FROM TICKET
> GROUP BY APPLET, INIT_ID
>
> Gives the error.
>
> Anybody seen this sort of problem?

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


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