Hello Alfred,

> I have a IB_Query, IB_DataSource and IB_Grid. My SQL in IB_Query is
> SELECT COY_NAME, CONTACT_PERSON, CONTACT_NO, FAX FROM CUSTOMER. I
> have OrderingItems and OrderingLinks setup so that I can sort
> COY_NAME or CONTACT_PERSON by clicking on the IB_Grid's title.
> Basically everything is working fine.
>
> After reading some tips on optimizing sql, I found out that using an
> index to perform ordering slows things down. And to avoid that, we
> can change the sql to ORDER BY COY_NAME||''. This will cause the
> optimizer to not use the index to sort.
>
> But I noticed that during OnPrepareSQL event, no matter which field
> I click to sort, ...

I don't understand this. How can you *click* on anything during an
OnPrepareSQL event? You write a handler and maybe change the order
there.

> ...the SQLOrder will not be updated. However sqlmonitor reveals that
> the actual statement executed does have an ORDER BY clause.

If you want to choose from your own predefined OrderingItems, all you
have to do is assign the 1-based item number to MyQuery.OrderingItemNo
(negate the number for a descending order, provided you have defined
one in the OrderingItem).

You don't really need the OnPrepareSQL event to assign the
OrderingItemNo - although you *can* do it in the OnPrepareSQL event
handler if that suits you.

If you want to assign an explicit ordering - any ordering - within
the OnPrepareSQL handler using SQLOrder, you must make sure that
OrderingItemNo is 0, or your SQLOrder will be overwritten by the one
corresponding to OrderingItemNo. Here's a piece of code from an
OnPrepareSQL handler of mine:

case stagesHuidigBedrijf:
qryStages->SQLWhereItems->Add( WIHuidigePeriode );
qryStages->SQLWhereItems->Add( "A.BEDRIJF_ID" +
( kolBID->IsNull ? EqNull : "=" + kolBID->AsString ) );
qryStages->OrderingItemNo = 0;
qryStages->SQLOrder->Text =
"ORDER BY A.BEDR_AFD_NAAM, L.ACHTERNAAM, L.VOLL_NAAM";
break;

At first I didn't have the "qryStages->OrderingItemNo = 0;" line in
there, but the monitor showed that the SQLOrder got overwritten if
there was an active OrderingItem. Evidently the OrderingItemNo is
interpreted (and its ORDER BY clause added) _after_ the OnPrepareSQL
event...


Greetings,
Paul Vinkenoog