DOCUMENT:Q133001 15-FEB-2000 [foxpro] TITLE :PRB: SQL SELECT Statement UDF Executes Twice On First Record PRODUCT :Microsoft FoxPro PROD/VER:MACINTOSH:2.6a; MS-DOS:2.6a; WINDOWS:2.6a,3.0 OPER/SYS: KEYWORDS: ====================================================================== ------------------------------------------------------------------------------- The information in this article applies to: - Microsoft Visual FoxPro for Windows, version 3.0 - Microsoft FoxPro for Windows, version 2.6a - Microsoft FoxPro for MS-DOS, version 2.6a - Microsoft FoxPro for Macintosh, version 2.6a ------------------------------------------------------------------------------- SYMPTOMS ======== A user-defined function (UDF) called as a select item in an SQL SELECT statement executes twice for the first record of the source table. CAUSE ===== The first iteration of the SQL SELECT statement creates and sizes the columns in the output. The first iteration therefore executes once to do this and once to determine what data to return from the first record. RESOLUTION ========== Insert code in the UDF to suppress its execution on the first iteration. Please see the examples in the More Information section. STATUS ====== This behavior is by design. MORE INFORMATION ================ Steps to Reproduce Behavior --------------------------- 1. Create a table called SQLUDF.DBF, and add a single numeric field called nField1. 2. Place the values 1, 2, 3, 4, and 5 in the first five records. 3. Run the following code: * start of SQLUDF.PRG * * This program selects records from the input table and calculates a * running total in the output table by using a UDF. nRunningTot = 0 && initialize running total to 0 nIterations = 0 && initialize iteration counter to 0 SELECT *, Add_Em(nField1) as nRunTotal FROM SQLUDF INTO CURSOR temp BROWSE NOWAIT PROCEDURE Add_Em PARAMETERS tnFld1 nIterations = nIterations + 1 && Increment iteration counter WAIT WINDOW "Record Number: "+ALLTRIM(STR(RECNO())) + ; CHR(13)+ "Number of Iterations: "+ALLTRIM(STR(nIterations)) ; TIMEOUT 1 nRunningTot=nRunningTot + tnFld1 && Calculate running total RETURN nRunningTot ENDPROCEDURE *End of SQLUDF.PRG SQLUDF.PRG Notes ---------------- - The Record Number displays 1 twice, then it displays 2 through 5. - The Number of Iterations displays 1 through 6. - The values placed in the RunTotal field are 2, 4, 7, 11, and 16 for records 1 through 5. The values should be 1, 3, 6, 10, and 15. Example Workaround ------------------ The following program is a modification of the above code. It suppresses the extra calculation for the first iteration of the UDF: * start of SQLUDF2.PRG * * This program sets a logical flag to test for the first iteration of * the UDF to suppress getting a sum from the first record twice. * nRunningTot = 0 && initialize running total to 0 nIterations = 0 && initialize iteration counter to 0 lFirst_Time = .T. && initialize 1st time through variable SELECT *, Add_Em(nField1) as nRunTotal FROM SQLUDF INTO CURSOR temp BROWSE NOWAIT PROCEDURE Add_Em PARAMETERS tnFld1 IF lFirst_Time != .T. && Only run if not 1st time through nIterations=nIterations + 1 && Increment iteration counter WAIT WINDOW "Record Number: " + ; ALLTRIM(STR(RECNO())) + ; CHR(13) +"Number of Iterations: " + ALLTRIM(STR(nIterations)); TIMEOUT 1 nRunningTot = nRunningTot + tnFld1 && Calculate running total ENDIF lFirst_Time=.F. && Togggle to .F. on 1st call to UDF RETURN nRunningTot ENDPROCEDURE *End of SQLUDF.PRG Workaround Notes ---------------- - The lFirst_Time variable causes the UDF to not create a running total when called the first time. - The Record Number displays 1 through 5. - The Number of Iterations displays 1 through 5. - The values placed in the RunTotal field are correct: 1, 3, 6, 10, and 15. General Notes ------------- - If record 1 of the source table does not satisfy a WHERE condition, the UDF is called once for the first record in spite of the workaround code. - The overriding emphasis remains that the behavior of UDFs called from SQL SELECT statements is unpredictable. Extensive testing should be done to ensure that results are as expected. Please see the subheading under SELECT - SQL in the FoxPro Language Reference for more information. REFERENCES ========== For more information about a similar behavior of a UDF, please see the following article in the Microsoft Knowledge Base: Q124402 Calling a User-Defined Function (UDF) from SQL SELECT Additional query words: VFoxWin FoxMac FoxDos FoxWin ====================================================================== Keywords : Technology : kbHWMAC kbOSMAC kbVFPsearch kbAudDeveloper kbFoxproSearch kbZNotKeyword3 kbFoxPro260aMac kbFoxPro260aDOS kbFoxPro260a kbVFP300 Version : MACINTOSH:2.6a; MS-DOS:2.6a; WINDOWS:2.6a,3.0 ============================================================================= THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Copyright Microsoft Corporation 2000.