Wiki Home

Udfs And Trims In Indexes


Namespace: WIN_COM_API
UDF stands for User Defined Function.

I ran into a newsgroup question which raised an issue not many people understand. Please see MS KB article Q129889 Offsite link to http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q129889.
SUMMARY
When creating an index key, it is important to remember that the index keys will have a fixed length even if the length of the field contents are varying lengths. FoxPro does not create or use variable-length keys. Index keys are padded with spaces to a constant size (the length of the field in the table structure).

You can use a User Defined Function (UDF) in an index expression. For example, you can order the records based on a partial field (for example, the street name in an address field). When a UDF is used to create an index key, the UDF is called twice to initiate the process before the records are processed. Then it is called once for each record in the table.

This article shows by example how to use a UDF in an index expression with the four trim functions.

MORE INFORMATION
The four trim functions, TRIM(), ALLTRIM(), RTRIM(), and LTRIM() are ignored during the first two passes through the UDF. Then they are used to generate the index keys. However, note that the resulting key is still padded with blanks to the constant length of the field in the index. It is still a fixed-length index.

When tracing the UDF through the TRACE WINDOW, the first two passes through the code, as the index is built, calculate the key expressions so all the functions for removing blanks are ignored. The remaining passes through the UDF build the actual index keys. During this phase of building the index keys, the trim functions operate as expected within the UDF.


When FoxPro builds an index which contains a UDF, it makes 3 passes through the process. In the first pass it determines the size of the field(s) in question. Then it evaluates the UDF without any TRIM() functions, and then on the third pass it TRIM()'s to get the actual index values.

If the lack of TRIM()ming on the first pass generates a zero length string, Invalid key length (Error 112) is generated.

The newsgroup poster had a complicated function which he wanted to use to index his field, and the following code shows (in the Fun fields) what the developer gets as he tests his UDF, and demonstrates (in the NoTrim fields) what FoxPro sees in the first pass of the indexing function.
CREATE CURSOR Dummy (Fy C(4), ;
	Fun1 C(10), Fun2 C(10), Fun3 C(10), ;
	NoTrim1 C(10), NoTrim2 C(10), NoTrim3 C(10))

INSERT INTO Dummy (Fy, Fun1, Fun2, Fun3, NoTrim1, NoTrim2, NoTrim3) ;
	VALUES ;
	("1999", ;
	SUBSTR("ADCBENMLKJIHGF", AT(RIGHT(ALLTRIM("1999"), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(LEFT(ALLTRIM("1999"), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(SUBSTR(ALLTRIM("1999"), LEN(ALLTRIM("1999")), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(RIGHT("1999", 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(LEFT("1999", 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(SUBSTR("1999", LEN("1999"), 1), "FXRM0123456789"), 1))

INSERT INTO Dummy (Fy, Fun1, Fun2, Fun3, NoTrim1, NoTrim2, NoTrim3) ;
	VALUES ;
	("2000", ;
	SUBSTR("ADCBENMLKJIHGF", AT(RIGHT(ALLTRIM("2000"), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(LEFT(ALLTRIM("2000"), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(SUBSTR(ALLTRIM("2000"), LEN(ALLTRIM("2000")), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(RIGHT("2000", 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(LEFT("2000", 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(SUBSTR("2000", LEN("2000"), 1), "FXRM0123456789"), 1))

INSERT INTO Dummy (Fy, Fun1, Fun2, Fun3, NoTrim1, NoTrim2, NoTrim3) ;
	VALUES ;
	("99  ", ;
	SUBSTR("ADCBENMLKJIHGF", AT(RIGHT(ALLTRIM("99  "), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(LEFT(ALLTRIM("99  "), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(SUBSTR(ALLTRIM("99  "), LEN(ALLTRIM("99  ")), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(RIGHT("99  ", 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(LEFT("99  ", 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(SUBSTR("99  ", LEN("99  "), 1), "FXRM0123456789"), 1))

INSERT INTO Dummy (Fy, Fun1, Fun2, Fun3, NoTrim1, NoTrim2, NoTrim3) ;
	VALUES ;
	("6/9  ", ;
	SUBSTR("ADCBENMLKJIHGF", AT(RIGHT(ALLTRIM("6/9  "), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(LEFT(ALLTRIM("6/9  "), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(SUBSTR(ALLTRIM("6/9  "), LEN(ALLTRIM("6/9  ")), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(RIGHT("6/9  ", 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(LEFT("6/9  ", 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(SUBSTR("6/9  ", LEN("6/9  "), 1), "FXRM0123456789"), 1))
	
INSERT INTO Dummy (Fy, Fun1, Fun2, Fun3, NoTrim1, NoTrim2, NoTrim3) ;
	VALUES ;
	("X   ", ;
	SUBSTR("ADCBENMLKJIHGF", AT(RIGHT(ALLTRIM("X   "), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(LEFT(ALLTRIM("X   "), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(SUBSTR(ALLTRIM("X   "), LEN(ALLTRIM("X   ")), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(RIGHT("X   ", 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(LEFT("X   ", 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(SUBSTR("X   ", LEN("X   "), 1), "FXRM0123456789"), 1))

INSERT INTO Dummy (Fy, Fun1, Fun2, Fun3, NoTrim1, NoTrim2, NoTrim3) ;
	VALUES ;
	("M   ", ;
	SUBSTR("ADCBENMLKJIHGF", AT(RIGHT(ALLTRIM("M  "), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(LEFT(ALLTRIM("M   "), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(SUBSTR(ALLTRIM("M   "), LEN(ALLTRIM("M   ")), 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(RIGHT("M  ", 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(LEFT("M   ", 1), "FXRM0123456789"), 1), ;
	SUBSTR("ADCBENMLKJIHGF", AT(SUBSTR("M   ", LEN("M   "), 1), "FXRM0123456789"), 1))

BROWSE


Where is/are the UDF(s) in the above?... Am I blind?


Contributor: Cindy Winegarden

See also Index Expression Requirements

Category Tips And Tricks Category Key Fields Category Data
( Topic last updated: 2005.05.05 09:47:48 AM )