inm logo
Corporate Website | Contact | Store | Support | Login  
     


Knowledgebase
View categorized listing of all common frequently asked questions.
Downloads
View our categorized library of downloads for all necessary manuals, software, etc.
 Article Options
Support Center » Knowledgebase » If fields from different tables have the same name, query results will be incorrect.
Support Page Banner
 If fields from different tables have the same name, query results will be incorrect.
Product:Platform:Area:Version:
INM VizionDB for DirectorAllDatabaseAll

In INM VizionDB version 4.0, if you create a multi-table Recordset, using an SQL statement that contains two fields with the same name, but from different tables, INM VizionDB will not return the correct results when using the VizionDB SnapGUI behaviors. In addition, the following Recordset methods: GetFieldName(), GetFieldValue(), GetFieldPosition() will be unreliable if there are two fields with the same name coming from different tables.

In the INM VizionDB Editor if you use the Query Designer to create a query that contains two fields from different tables that have the same name, your results will be incorrect.

 This is a known issue that should be resolved in the next update of the Xtra.

Workaround:


If you already have your database structure defined with identical names in two different tables you can work around this by using aliases within your SQL statement and by parsing the property list returned by GetRecords(). This list will contain the #alias property that you have defined in your SQL statement, and the values returned are correct.

Example 1 (same field name “NAME” used in two tables, no aliases):

SELECT STUDENTS.STUDENT_ID, STUDENTS.NAME, COUNTRIES.COUNTRY_ID, COUNTRIES.NAME FROM STUDENTS INNER JOIN COUNTRIES ON STUDENTS.COUNTRY_ID=COUNTRIES.COUNTRY_ID ORDER BY STUDENTS.STUDENT_ID ASC

Incorrect results returned:

1

Caroline

38

Caroline

2

Drew

38

Drew

3

Charles

224

Charles

4

Jackie

225

Jackie

5

Valerie

225

Valerie

Expected results:

1

Caroline

38

Canada

2

Drew

38

Canada

3

Charles

224

United Kingdom

4

Jackie

225

United States

5

Valerie

225

United States

Example 2 (same field name “NAME” used in two tables, but with aliases “STUDENT_NAME” and “COUNTRY_NAME”):

SELECT STUDENTS.STUDENT_ID, STUDENTS.NAME AS Student_Name, COUNTRIES.COUNTRY_ID, COUNTRIES.NAME AS Country_Name FROM STUDENTS INNER JOIN COUNTRIES ON STUDENTS.COUNTRY_ID=COUNTRIES.COUNTRY_ID ORDER BY STUDENTS.STUDENT_ID ASC

This returns correct Results in GetRecords(), but still incorrect in the INM VizionDB Editor:

-- [#fields: [[#name: "STUDENT_ID", #alias: "STUDENT_ID", #type: #integer, #size: 4], [#name: "NAME", #alias: "STUDENT_NAME", #type: #varchar, #size: 50], [#name: "COUNTRY_ID", #alias: "COUNTRY_ID", #type: #integer, #size: 4], [#name: "NAME", #alias: "COUNTRY_NAME", #type: #varchar, #size: 255]], #data: [[1, "Caroline", 38, "Canada"], [2, "Drew", 38, "Canada"], [3, "Charles", 224, "United Kingdom"], [4, "Jackie", 225, "United States"], [5, "Valerie", 225, "United States"]]]

 


Product:Platform:Area:Version:
INM VizionDBAllRecordset SQL4.0



Article Details
Article ID: 34
Created On: 31 May 2006 10:58 AM

 This answer was helpful  This answer was not helpful

Posted By: Jo On: 04 Jul 2006 11:54 AM
this is my comment
Posted By: Jo Le (jo@inm.com) On: 04 Jul 2006 11:55 AM
this is another comment
Posted By: Jooo Leee (joooleee@inm.com) On: 04 Jul 2006 11:57 AM
hmmm
inm general footer
Services Xtras Go Products Support Gallery Store Download About Us Contact Newsroom