bwc3068 wrote:

> i've got a query that has a persistent field in it called: OwnerName.
> it's a calculated field.
>
> It's in the equipment query/table that holds all the equipment and one
> of the other fields in the equipment query is the uniquekey of the owner
> in the owner table.
>
> as you can guess, the calculated field OwnerName uses the equipment
> table's owner-table uniquekey to look up the owner name in the owner table.
>
> when i try and sort by OwnerName, I get an error. Can I sort by the
> calculated field when it's persistent like that (I'm pretty sure i could
> sort by the calculated field if it was something like: order by
> (count(employees)). that is, something calculated right there in the
> order by clause.
>
> this is the .OnGetText of the calculated field:
>
> procedure TdmMain.OwnerNameGetText(Sender: TField;
> var Text: String; DisplayText: Boolean);
>
> begin
> with wqryTemp do begin
> active := False;
> sql.clear;
> sql.add('select OwnerName, UniqueKey from Owners where UniqueKey
> ='+quotedstr(sEquipmentTablesOwnerUniqueKey));
> try
> active := True;
> sCurrentOwnerName := FieldByName('OwnerName').text;
> active := False;
> except
> end;
> end;
> text := sCurrentOwnerName;
> end;
>
> thoughts?
>
> thanks
> kelly

The field is calculated in your Delphi application by your Delphi code
at the client side. The SQL is executed by the Firebird server at the
server side. Consequently Firebird has no idea that the calculated field
even exists. Only your Delphi code knows about it.

You either need to use Delphi's client-side sorting (if the components
you use support that), or construct the complete query in SQL, so it's
all executed at the server.

May I suggest a join between your equipment table and the owner table:

select Owners.OwnerName, Equipment.*
from Owners
inner join Equipment
on Equipment.OwnerUniqueKey = Owners.UniqueKey
where (your selection criteria)
order by Owners.OwnerName

This is the most common way to do it, btw.

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64