Hi,
now I have an idea; a bit complicated to explain, but does any one
know how I could achieve the following FROM WITHIN A TRIGGER:

-get the collation key of a varchar object
-convert the key into a one-byte-per-char string (i.e. each character
is "calculated" by using the byte as and index into ascii)
-append a 4-byte (big) integer object likewise to this string

the thing is, i am really using NOT unicode_fss collation, but my own
unicode collation which i wrote according to David Schnepper's paper
(see here:
http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_collation); i
included unicode_fss in the script so no one gets confused by my
collation's name...
Now, my collation GUARANTESS that the collation key of a unicode_fss
object is exactly as long (in bytes) as there are number of characters
(NOT BYTES) in the unicode_fss string. So an 80-character string would
have a collation key of 80 bytes, no more...
And my idea is to introduce a new field in table dicentries which
would be calculated from column asterm and id each time an entry in
dicentries is inserted or modified
Assuming this new field was called "ASTERMCOLLATE", instead of writing
"order by asterm, id", I could simply write "order by
ASTERMCOLLATE"..and because this column could be sorted by codepoint,
it would even be possible to easily create an index on it

I hope some of you can help me,
thanx,

Martin

--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:
>
> --- In firebird-support@yahoogroups.com, "martinknappe" <martin@>
> wrote:
> >
> > Ok, thank you..now I really have a problem
> > What I *could* of course do is only create my 1-field index on asterm
> > and then instead of writing
> >
> > "order by asterm ascending, id ascending"
> >
> > write
> >
> > "order by asterm"
> >
> > but the problem I'm having with this approach is the following:
> >
> > what happens when there are 2 or more records with the same value for
> > asterm?
>
> You can make no assumptions about the order of those records. Two or
> more records with the same asterm value will be returned in an
> arbitrary order that may change between one version of Firebird and
> the next, or even when an index statistics are recalculated and
> another plan is then used.
>
> So yes, you are in a bit of a pickle there, you have reached a
> limitation of Firebird 1.5. Firebird 2 increases this maximum index
> size to 25% of the page size, but that is not a huge help today.
>
> Adam
>