Ah, ok, that explains it; thanx for the time
Martin

--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:
>
> --- In firebird-support@yahoogroups.com, "martinknappe" <martin@>
> wrote:
> >
> > > You seem to be expecting a case insensitive sort, which you will
not
> > > get unless you define such a collation or use the upper.
> >
> > Not at all. All I was expecting is that doing "order by asterm
> > ascending, id ascending" on something that's "ordered by asterm
> > descending, id descending" would revert the order. I might e.g. have
> > an ordered-by-asterm-ascending,-id-ascending dataset like the
following
> >
> > asterm id
> > a 1
> > a 2
> > b 3
> > b 5
> > b 7
> > c 4
> > c 8
> >
> > an order by asterm descending, id descending on that dataset should
> > return the converse order no matter what collation im using,
> shouldn't it?
> >
>
> I am using your stored procedure here and the original example:
>
> select * from get_prior_20('Autowaschanlage, f', 47);
>
> Inside the stored procedure, the first query to be run is:
>
> for select first 20 id, asterm from
> dicentries
> where asterm = :astermin and id <= :idin
> order by asterm descending, id descending
>
> In this case, the record returned is (there is only 1)
>
> ID ASTERM
>
> ======== ===============================================
> 47 Autowaschanlage, f
>
>
> Suspend is called once, returning this value
>
> The next query you run is
>
> select first 20 id, asterm from
> dicentries
> where asterm < :astermin
> order by asterm descending, id descending
>
> In this case, there are 20 records returned, but due to your logic
> inside your for loop, cnt = 20 will cause it to exit before it returns
> the final record.
>
> ID ASTERM
>
> ======== ================================================
> 40 Autowachs, n
>
> 39 Autostaubsauger, m (2)
>
> 38 Autostaubsauger, m (1)
>
> 36 Autoradio, n
>
> 32 Autopolitur, f
>
> 31 Automobilklebstoff, m
>
> 16 automatisches Getriebe, n
>
> 149 automatischer Blockierverhinderer, m ABV
>
> 28 automatische Umluftregelung, f
>
> 27 Automatische Umluft Control, f
>
> 26 automatische UKW-Stör-Unterdrückung, f
>
> 25 automatische Bremsanlage, f
>
> 22 automatische Abisolierzange, f
>
> 29 automatisch zuschaltender Allradantrieb, m
>
> 188 Automatikgurt, m
>
> 14 Automatikgetriebe, n
>
> 187 Automatikantenne, f
>
> 189 Automatik-Sicherheitsgurt, m
>
> 23 Automatik-Abisolierzange, f
>
> 12 Automatik, m (2)
>
>
> So, your stored procedure without an order by returns the records:
>
> ID ASTERM
>
> ========
===========================================================
> 47 Autowaschanlage, f
>
> 40 Autowachs, n
>
> 39 Autostaubsauger, m (2)
>
> 38 Autostaubsauger, m (1)
>
> 36 Autoradio, n
>
> 32 Autopolitur, f
>
> 31 Automobilklebstoff, m
>
> 16 automatisches Getriebe, n
>
> 149 automatischer Blockierverhinderer, m ABV
>
> 28 automatische Umluftregelung, f
>
> 27 Automatische Umluft Control, f
>
> 26 automatische UKW-Stör-Unterdrückung, f
>
> 25 automatische Bremsanlage, f
>
> 22 automatische Abisolierzange, f
>
> 29 automatisch zuschaltender Allradantrieb, m
>
> 188 Automatikgurt, m
>
> 14 Automatikgetriebe, n
>
> 187 Automatikantenne, f
>
> 189 Automatik-Sicherheitsgurt, m
>
> 23 Automatik-Abisolierzange, f
>
>
> Further investigation shows that you *** DO *** use a collation, and
> as I told you before, Collations can allow for case insensitive
sorting.
>
> dicentries.ASTERM is defined as a field of type S_CHAR_80
>
> This is a domain declared by you (or someone you work with) as follows:
>
> CREATE DOMAIN T_CHAR_80 AS VARCHAR(80) COLLATE PXW_INTL850;
>
> That means that the two internal queries of the stored procedure sort
> in a case insensitive fashion.
>
> When you change your original query to:
>
> select * from get_prior_20('Autowaschanlage, f', 47) order by
> astermout ascending, idout ascending
>
> You are ordering by ASTERMOUT (the output parameter of the stored
> procedure), not by the field you happen to use inside the stored
> procedure to assign values to this parameter. There is no reason you
> can not output a fixed string or some other field to that parameter if
> you had wanted to (providing it of course fits inside a varchar(80)).
>
> The parameter is defined as
>
> ASTERMOUT VARCHAR(80)
>
> This does not have a collation like your table, so it will order the
> results case sensitive! I have not seen any syntax to assign a
> collation to an output variable, but you can define one in your select.
>
> To compare apples with apples, I would expect the following query to
> return it in reverse order.
>
> select * from get_prior_20('Autowaschanlage, f', 47) order by
> astermout COLLATE PXW_INTL850 ascending, idout ascending
>
> But otherwise, you can't expect two queries ordering based on rules of
> two different collations to come up in the reverse order because you
> switched descending and ascending everywhere. Once they are the same
> collation, you can make that assumption.
>
> Adam
>