How to call a user-defined function from the SELECT-SQL statement in FoxPro

This article was previously published under Q124402
This article shows by example how to add functionality to a StructuredQuery Language SELECT-SQL command by calling a user-defined function(UDF) from the SELECT-SQL statement.
Assume that you want to create a query that will produce three or fewerrecords for each state in the customer table.

Using the customer table in the tutorial directory and the code from the"Sample Code" section of this article, you can specify how many recordsfrom each state are to be included in the resulting query. The variablemax_match is set to 3, but as not all states have 3 or more records, theresulting query won't have 3*49 records. This example will produce 116records.

When max_match is set to 2, the result contains 86 records. When set to 1,the result contains 49 records, which is the same number of records the'GROUP BY state' clause returns.

Step-by-Step Example

  1. Create a file that contains the code listed in the "FoxPro 2.x Sample Code" or Visual FoxPro Sample Code sections of this article, and save it as MAIN.PRG.
  2. From the FoxPro command line, type "DO main" without the quotation marks. Then press ENTER to see the results.

FoxPro 2.x Sample Code

CLEARCLEAR ALLSET DEFAULT TO SYS(2004)+"tutorial"match=0max_match=3prev_state="None"first_call=.T.SELECT state FROM customer ORDER BY state INTO CURSOR tmp1SELECT state FROM tmp1 WHERE mycount()FUNCTION mycountDO CASECASE first_call                                   && Condition  1  first_call = .F.CASE (state = prev_state) AND (match < max_match) && Condition  2  match = match + 1CASE state != prev_state                          && Condition  3  prev_state = state  match = 1CASE match >= max_match                           && Condition  4  RETURN .F.OTHERWISE  WAIT WINDOW "Untested condition occurred, result may not be correct!"ENDCASERETURN .T.				

Visual Foxpro Sample Code

   CLEAR   CLEAR ALL   SET DEFAULT TO SYS(2004)+"Samples\Data"   match=0   max_match=3   prev_to_country="None"   first_call=.T.   SELECT to_country FROM orders ORDER BY to_country INTO CURSOR tmp1   SELECT to_country FROM tmp1 WHERE mycount()   FUNCTION mycount   DO CASE   CASE first_call                                         && Condition  1     first_call = .F.   CASE (to_country=prev_to_country) AND (match<max_match) && Condition  2     match = match + 1   CASE to_country != prev_to_country                      && Condition  3     prev_to_country = to_country     match = 1   CASE match >= max_match                                 && Condition  4     RETURN .F.   OTHERWISE     WAIT WINDOW "Untested condition occurred, result may not be correct!"   ENDCASE   RETURN .T.				

What the Code Does

The first few lines in the Main program clear the environment andinitialize a few global variables to be used in the mycount() UDF. Thefirst SELECT-SQL command creates a sorted CURSOR (tmp1) for the second SELECT-SQLcommand to use. The mycount() UDF is called once for each of the records inthe tmp1 CURSOR. Each time the UDF returns TRUE, the current record in tmp1is included in the resulting query.

There are four conditions in the UDF:
  1. The first condition is only used once. It allows the first record in the tmp1 CURSOR to be tested. Without this condition, the first record in tmp1 would be ignored.
  2. The second condition lets the second record and the third record for each state or To_country to be included.
  3. The third condition lets the first record for each new state or To_country to be included.

    Note For this condition to correctly work, the table must be sorted.
  4. The fourth condition causes the current record in tmp1 to be skipped. Please note that this condition is only checked when all the previous conditions evaluate to false. The Else is added for error checking.
VFoxWin FoxWin 2.50

ID d'article : 124402 - Dernière mise à jour : 02/12/2007 18:25:26 - Révision : 3.4

Microsoft Visual FoxPro 9.0 Professional Edition, Microsoft Visual FoxPro 8.0 Professional Edition, Microsoft Visual FoxPro 7.0 Professional Edition, Microsoft Visual FoxPro 3.0 Standard Edition, Microsoft FoxPro 2.5b, Microsoft FoxPro 2.5a, Microsoft FoxPro 2.5b, Microsoft FoxPro 2.6 Standard Edition, Microsoft FoxPro 2.6a Standard Edition

  • kbcode KB124402