>David,
>
>>>I certainly would agree if the values didn't change much from record to record. But they do.
>It's a udf which converts a coded field into meaningful English, eg 1-Low 2-Medium 3-High. So I don't see a way around that issue. It's different on every record. Performance is pretty good actually.>>
>
>
>Yes, but in this case, the UDF is performing the same functionality as a pair of tables that have been normalized to second normal form (???)
>
>From my normalization rules, conveniently posted on my wall:
>
>Second Normal Form - Eliminate Redundant Data
>· Create separate tables for sets of values that apply to multiple records.
>· Relate these tables with a foreign key.
>
>
>MainDataTable
>
>Field1 Field2 FldStatus
>>data1> >data2> L
>>data1> >data2> M
>>data1> >data2> L
>>data1> >data2> H
>etc...
>
>StatusTable
>
>FldStatus Desc
>L Low
>M Medium
>H High
>
>
>USE StatusTable IN 0 ORDER FldStatus
>
>SELECT MainDataTable
>SET RELATION TO FldStatus INTO StatusTable
>
>
>
>The above tables and code perform the same functionality as:
>
>FUNCTION StatDesc(tcCode)
>LOCAL lcRetCode
>
>DO CASE
> CASE tcCode = "M"
> lcRetCode = "Medium"
> CASE tcCode = "H"
> lcRetCode = "High"
> OTHERWISE
> lcRetCode = "Low"
>ENDCASE
>
>RETURN(lcRetCode)
>
>
>If this kind of stuff was being done in a fairly low-level language (i.e. C) the performance between the two methodolgies might be comparable and the effort required to write the translation function would be less than writing the data handing... But we're not talking about C. We're talking about VFP which is first and formost a DBMS. With VFP, setting the relationship between the two tables is going to yield performance thats two maybe three ORDERS OF MAGNITUDE (thats hundreds to thousands of times) faster. And while in my previous post I made the lookup table a TABLE, in actual practice, I would do something like:
>
>
>SELECT APN AS Status, SPACE(25) AS FullStatus ;
>FROM MyTable ;
>GROUP BY Status ;
>INTO CURSOR ShwSt
>
>USE DBF() AGAIN IN 0 ALIAS ShowStatus
>USE IN ShwSt
>
>UPDATE ShowStatus ;
>SET FullStatus = GetDesc(ShowStatus.Status, "APN")
>
>Which makes the cursor read/write for the SQL UPDATE, but removes the cleanup overhead of getting rid of the temporary cursor "ShowStatus"
>
>
>>>I certainly would agree if the values didn't change much from record to record. But they do.
>
>
>Actually, this kind of scenario is one of the major reasons for table normalization.
>
>It works really well. Promise :-)
>
>
>Regards,
>Thom C.

You're right Thom, that will perform much better for the reasons you state and is "correct".
Thanks for that