> The ORDER BY has to correspond exactly to an index for it to be a live
> query. IOW, a live query is just like a tTable.

Well, in this case it actually does - ie there IS a "boxnumber" index. It
is a single text field, case-insensitive, not unique.


Kerry Neighbour

> Wish you had given your "Actual" query first time around, then we
> would have said very quickly that you probably dont have the correct
> index on BoxNumber,

Good point. But as it turns out, I DO have an index on boxnumber. You say
"correct" index - does this imply that only certain indexes will work?  It
is not a compound index, id that is what you mean.


Kerry Neighbour

Kerry,

<< Well, in this case it actually does - ie there IS a "boxnumber" index. It
is a single text field, case-insensitive, not unique. >>

If it is case-insensitive, then your ORDER BY must be:

SELECT id,boxnumber,datestring FROM boxes ORDER BY boxnumber NOCASE

See here:

http://www.elevatesoft.com/dbisam4d5_live_queries_and_canned_querie.htm

--
Tim Young
Elevate Software
www.elevatesoft.com

Kerry Neighbour <kneighbour@securedoc.com.au> wrote on Thu, 25 May 2006 19:36:59 -0400

>
>> The ORDER BY has to correspond exactly to an index for it to be a live
>> query. IOW, a live query is just like a tTable.
>
>Well, in this case it actually does - ie there IS a "boxnumber" index. It
>is a single text field, case-insensitive, not unique.
>
>
>Kerry Neighbour
>
>
Kerry


AHA!!  So the case-insensitive index is the problem!

"SELECT id,boxnumber,datestring FROM boxes ORDER BY UPPER(boxnumber)"

is what you want.

Cheers

Jeff
--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

Jeff Cook <jeffc@aspect.co.nz> wrote on Fri, 26 May 2006 11:50:01 +1200



>
>AHA!!  So the case-insensitive index is the problem!
>
>"SELECT id,boxnumber,datestring FROM boxes ORDER BY UPPER(boxnumber)"
>
>is what you want.
>
>

Yer!  Wot Tim sez is rite!  NOCASE is the fing!

Cheers

Jeff

P.S.  I knew that, I knew that, I knew that, I knew that, I knew that .....

--
Jeff Cook
Aspect Systems Ltd
Phone: +64-9-424 5388
Skype: jeffcooknz
www.aspect.co.nz

>
> If it is case-insensitive, then your ORDER BY must be:
>
> SELECT id,boxnumber,datestring FROM boxes ORDER BY boxnumber NOCASE

Excellent!!!!

This "problem" has bugged me for ages (years?). Sometimes it would work,
sometimes not. I now see why not. Frankly, my SQL background is MSAccess,
and MSAccess is not so restrictive with live queries.

Thanks to all for the quick help.

Kerry Neighbour

I think Tim Answered that one above,
Cheers
Clive.

"Kerry Neighbour" <kneighbour@securedoc.com.au> wrote in message
news:869BC498-42AB-486C-8932-71580DBD3E2A@news.elevatesoft.com...
>
>> Wish you had given your "Actual" query first time around, then we
>> would have said very quickly that you probably dont have the correct
>> index on BoxNumber,
>
> Good point. But as it turns out, I DO have an index on boxnumber. You say
> "correct" index - does this imply that only certain indexes will work?  It
> is not a compound index, id that is what you mean.
>
> Kerry Neighbour
>
>