At 08:35 PM 24/06/2005 +0000, you wrote:

>I have a query with the following script
>select pkey
> ,afield
> ,lkey
> ,yesno
> , (select lname
> from ltable
> where ltable.pkey=mtable.lkey) as lname
>from mtable
>
>my question is there a way to set the ordeing on lname without ibo
>responding with an error "SQl error -206 Column unknown"
>I want the user to be able to search and sort on lname

Sort on - yes. Search on - no - at least not effectively. Searching is
done on database columns, not virtual output fields (as this one is). Your
syntax for the correlated subquery is a bit "out", but this way of sucking
values in from another table is very costly on resources unless the set is
well limited.

And - although it's possible to order by a virtual field (by referring to
its degree number, 5 in this case) the handy OrderingItems and other tricks
for managing sets in your interface really need to be set up as indexed
columns...that means they should be be database columns.

You can get this set using a join, which will enable you to order on that
field. You should make any field you are going to order by a non-nullable
one, as nulls tend to mess up orderings and searches unless you're very
careful. For similar reasons, it's best to avoid outer joins on such sets
if your requirement is to search on columns on the right side of a left
join or the left side of a right join.

Your inner join to get the above set would be:

select
m.pkey,
m.afield
m.lkey
m.yesno,
l.lname
from mtable m
join ltable l
on l.pkey=m.lkey

Now - with one or more good indexes on lname, you will have no trouble
setting up and using this field as an OrderingItem for searches and orderings.

Helen