At 11:17 AM 21-05-02 +0800, you wrote:

>Dear All,
>
>I've got a problem with my query
>" SELECT ID, UNICODE_FIELD FROM LANGUAGE ORDER BY UNICODE_FIELD " == > it's
>ok
>
>" SELECT ID, UNICODE_FIELD FROM LANGUAGE ORDER BY UPPER(UNICODE_FIELD) " ==
> > error

order by the indexed field and order by the calculated field are NOT the
same thing. If you order by the indexed field, you will get your mixed
case ordering, as you correctly supposed. If you order by the calculated
field, you will get the upper-case order. So it depends on what you
want...if you want the indexed order (and faster output) do
ORDER BY UNICODE_FIELD

If you want the uppercase order, do
ORDER BY 1 (use the number that represents the position of the field in the
the output set). It will be slow on a large output set because it has no
index to use.

What most people do when they want case-insensitive matching and sorting is
to define a proxy ordering column that is populated by BI and BU triggers
that do this:
...
if (new.originalcolumn is not null) then
new.proxycolumn = upper(originalcolumn);
...

Instead of indexing the original column, place your indexes on the proxy
column.

It's a very fast and bomb-proof solution which totally avoid the function
call on each row when forming output for selects.

cheers,
Helen


All for Open and Open for All
Firebird Open SQL Database · http://firebirdsql.org ·
http://users.tpg.com.au/helebor/
_______________________________________________________