You mentioned you cand o it by join. I do it this way.
I have a table called GetText
TableName varchar(put desired length here)
ColumnName varchar(put desired length here)
LookUpValue int or if you prefer char(1)
DisplayValue varchar(put desired length here)

I make join with this table if I want the values in views fro example,
but I use in the major time in Delphi Calculated Fields.

I have a Delphi Derived ComboBox that has new properties
LookUpTableName (the table name that holds all lookup values, in this
example "GetText")
LookUpColumn (here I put the column name that holds lookup data, in this
example "LookUpValue")
DisplayColumn (here I put the column name that holds display data, in this
example "DisplayValue")

As you can see you have a unique place that holds all translations, you can
use it in the client application, or in views.

I think the UDF like will be a good thing to make the view code cleaner and
easier to read, but _I THINK_ that if you put some code like this in a UDF
you will have to make the UDF conect to database (login, etc) and send a
select statment, get the value, close the conection return the value to FB.
I think this is very bad (and slow, and use lots of unecessary resource).

But, if you create an Stored Procedure like this:

create procedure looktext (TABLENAME varchar(50), COLUMNNAME varchar(50),
LOOKUPVALUE smallint)
returns (DISPLAYVALUE varchar(50))
as
begin
select
DisplayValue
from
GetText
where
TableName = :TableName and
ColumnName = :ColumnName and
LookUpValue = :LookUpValue
into
:DisplayValue;

suspend;
end

You could send an SQL Statment like this:


select
F.Name, F.Phone,
(Select DisplayValue from LookText('Friend', 'Title', F.Title))
from
Friend F

wich returns the friend name, phone number and the "Text" linked to the
number stored in Friend.Title column.


if you create a view like this:

Create View vFriend (Name, Phone, Title) as
select
F.Name, F.Phone,
(Select DisplayValue from LookText('Friend', 'Title', F.Title))
from
Friend F

It's easier to use like this

Select * from vFriend

Will return all your friends with Title (as text). Just easy.

I hope FB gurus coment on this, what performance penalties, and other bad
things could happen if this kind of stuff is used ?

see you

Alexandre Benson Smith
Development
THOR Software e Comercial Ltda.
Santo Andre - SP - Brazil
www.thorsoftware.com.br

At 17:49 14/03/02 +0100, you wrote:

>Hi All,
>I would like to ask you the following question:
>
>is possible to call SQL query within UDF?
>I have the idea, that there is for example table
>
>CREATE TABLE MYTABLE (
> MYKEY INTEGER,
> CONTACT_NAME VARCHAR(30),
> CONTACT_TYPE CHAR(2)
>)
>
>Suppose, that the CONTACT_TYPE contains values FR, FA, OT and the
>description field contains this:
>
>Friend=FR
>Family=FA
>Other=OT
>
>I would like to create UDF LOOKUP(FIELDNAME, FIELDVALUE) which should look
>for the description field and find appropriate text (for example
>LOOKUP(MYTABLE.CONTACT_TYPE, 'FR') should return string 'Friend'). Inside
>the UDF I need to call SQL to receive the description for the desired field
>and that is the question -> can I make such a call?
>
>I know, that it could be done by another table and join, but this UDF could
>be very handy when you can only define the texts in description field and
>only call the UDF to receive the text. One should not have to define lots of
>lookup tables in such case.
>
>What do you think about it?
>Ales Kahanek
>ales.kahanek@...
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/