Thanks, this sounds very good. I tried to solve this in a similar manner and
there are no substantial performance penalties I can see. I am convinced,
that this solution can simplify the process of DB design and maintenance.
UDF would not be the best in this case...
Ales

> -----Original Message-----
> From: Lista de Discuçao Interbase [mailto:listaib@...]
> Sent: Thursday, March 14, 2002 9:49 AM
> To: ib-support@yahoogroups.com
> Subject: Re: [ib-support] SQL in UDF
>
>
> 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
>