Queries and functions which accept alias as parameter

Submitted by Sergey on April 25, 2008 - 11:51 UTC
topic: 
sql
vfp
foxpro

The functions such as DELETED(), RECNO(), etc., which accept an optional workarea alias, could yield unexpected results when used in queries incorrectly.

Single-table queries that use these functions without an optional alias will return proper results. When alias is supplied, the result is unpredictable because of the way VFP SQL Engine (SQLE) opens tables for a query.
Multiple-table queries that use those functions (even without an optional alias) in most cases will return unexpected and incorrect results.


Note 1: Some background on how VFP SQL Engine works with tables. If a table is not opened already it'll be open in usual manner and left open after query is completed. The already open tables are not used by SQLE. They're opened under other aliases similar to USE...AGAIN...ALIAS, used in the query and closed afterwards. As result, an alias specified in above mentioned functions may refer either to a table used in a query or to the original table that is not used in the query. Code below demonstrates that. 




 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27




CLEAR
CREATE Table Test01 (PK I, Txt C(10))
INSERT INTO Test01 VALUES(1, "One")
INSERT INTO Test01 VALUES(2, "Two")
INSERT INTO Test01 VALUES(3, "Three")
USE IN Test01

SELECT RECNO("Test01") AS RnAlias, RECNO() AS Rn, * FROM Test01 INTO CURSOR crsNotOpenBefore
LIST OFF
* Result 1:  RnAlias = RN
     RNALIAS         RN          PK TXT       
           1          1           1 One       
           2          2           2 Two       
           3          3           3 Three      

* Move pointer in the original table to the record # 2
GOTO 2 IN Test01 

SELECT RECNO("Test01") AS RnAlias, RECNO() AS Rn, * FROM Test01 INTO CURSOR crsOpenBefore
LIST OFF
* Result 2: RnAlias references original table 
     RNALIAS         RN          PK TXT       
           2          1           1 One       
           2          2           2 Two       
           2          3           3 Three

Note 2: A name of derived table and a table alias specified in a query in AS clause are not workarea aliases. They both are internal to SQLE.

Comments