Kbase P166231: 4GL/ABL: Error (3166) doing a UNIQUE FIND against the _File table using _File-Name.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
5/17/2010 |
|
Status: Unverified
SYMPTOM(s):
4GL/ABL: Error (3166) doing a UNIQUE FIND against the _File table using _File-Name.
More than one <table> records found by a unique FIND. (3166)
The ambiguity error (3166) is generated when executing the following UNIQUE FIND statement:
FIND _File NO-LOCK WHERE _File-Name = "Customer".
The ambiguity error (3166) may also be generated when executing the following UNIQUE FIND statement:
FIND _File NO-LOCK WHERE _File-Name = "Customer" AND _Tbl-Type = 'T'.
FACT(s) (Environment):
Executing the following FOR statement returns more than one record:
FOR EACH _File NO-LOCK WHERE _File-Name = "Customer":
DISPLAY _File-Name.
END.
All Supported Operating Systems
Progress 9.x
OpenEdge 10.x
CAUSE:
This is expected behavior. The _File table stores the information on all tables, SQL VIEWs and Virtual System Tables in the database. Each SQL storage object created, be it an SQL VIEW or an SQL TABLE, has its own record in the _File table with the _File-Name as one of the fields in that record. Hence, doing a 4GL/ABL FIND using the _File-Name only, would succeed ONLY if we do not have SQL VIEWs or SQL TABLEs having the same name as the $GL database table used in the find. For example, the statement:
FIND _File NO-LOCK WHERE _File-Name = "Customer".
would generate error (3166) if one or more SQL VIEWs or SQL TABLEs have been created with the name 'Customer'. And the statement:
FIND _File NO-LOCK WHERE _File-Name = "Customer" AND _Tbl-Type = 'T'.
would generate error (3166) if one or more SQL SQL TABLEs have been created with the name 'Customer'.
FIX:
Change the names of the SQL VIEWs and SQL tables to be different from the names of the 4GL tables in the database. If changing the names of the SQL VIEWs and TABLEs is not feasible, then qualify the FIND with table owner. For example:
FIND _File NO-LOCK WHERE _File-Name = "Customer" AND _Owner = "PUB".
The above statement is guaranteed to succeed because we can not create an SQL VIEW owned by PUB nor can we create an SQL TABLE owned by PUB and having the same name as that of an existing 4GL table which is already owned by PUB.