> -----Original Message-----
> Well, Si, I do not know why you got your error and I don't know what
> it means. I'm confused what DISTINCT means when fields are NULL. I
> would believe NULLs to never be equal to eachother, so that DISTINCT
> will only reduce the number of returned rows for rows having
> values in
> all fields. Though I'm talking way over my head and have no idea how
> Firebird does this. Hopefully, someone else can enlighten us in this
> area.

The worst of it is, I can't even replicate the problem which made me use
DISTINCT in the first place anymore :-)

> Looking at your query, I can see that some of your JOINs actually are
> inner joins rather than left joins (either that or you want (UTUG.
> USER_ID = 1) in your LEFT JOIN rather than in your WHERE clause). Try
> changing your query to
>

[...]

>
> If there are no NULLS or users that aren't in P.USERID or P.
> LAST_EDITED_BY, you may even avoid LEFT JOINs altogether, and if not
> an alternative would be to change to subselects for those two
> USERNAME-fields.

Your right there, I missed the boat on that one. I will change the sql and
test it.

FWIW, the distinct still raised the existing exception though :-(

> In my opinion, LEFT [OUTER] JOIN should only be used when there is a
> good reason not to use the simpler [INNER] JOIN.

Agreed, will now have to recheck all my joins, what fun :-|

Rgds

Si Carter
http://www.fbtalk.net/ - Web Based Firebird Forum
http://sourceforge.net/projects/fbutils - FBUtils
http://sourceforge.net/projects/dnfbb - Web based, Firebird Powered Forum