Calling a User-Defined Function (UDF) from SQL SELECT

ID: Q124402

2.50 2.50a 2.50b 2.60 2.60a 3.00 WINDOWS

The information in this article applies to:

• Microsoft Visual FoxPro for Windows, version 3.0
• Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6, 2.6a

SUMMARY

This article shows by example how to add functionality to a Structured Query Language (SQL) SELECT command by calling a user-defined function (UDF) from the SELECT.

MORE INFORMATION

Assume that you want to create a query that will produce three or fewer records 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 records from each state are to be included in the resulting query. The variable max_match is set to 3, but as not all states have 3 or more records, the resulting query won't have 3*49 records. This example will produce 116 records.

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.

   marks. Then press ENTER to see the results.

FoxPro 2.x Sample Code

CLEAR CLEAR ALL SET DEFAULT TO SYS(2004)+"tutorial" match=0 max_match=3 prev_state="None" first_call=.T.

SELECT state FROM customer ORDER BY state INTO CURSOR tmp1 SELECT state FROM tmp1 WHERE mycount()

FUNCTION mycount

DO CASE

CASE first_call                                   && Condition  1
  first_call = .F.

  match = match + 1
CASE state != prev_state                          && Condition  3
  prev_state = state
  match = 1
CASE match >= max_match                           && Condition  4
  RETURN .F.

  WAIT WINDOW "Untested condition occurred, result may not be correct!"

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 and initialize a few global variables to be used in the mycount() UDF. The first SELECT command creates a sorted CURSOR (tmp1) for the second SELECT command to use. The mycount() UDF is called once for each of the records in the tmp1 CURSOR. Each time the UDF returns TRUE, the current record in tmp1 is 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.

   to be included.

   included. Please note that for this condition to work correctly, the
   table must be sorted.

   Please note that this condition is only checked when all the previous
   conditions evaluate to false. The Else is added for error checking.

Keywords          : kbcode FxprgSql 
Version           : 2.50 2.50a 2.50b 2.60 2.60a 3.00
Platform          : WINDOWS