- Feb 1, 2021
- 38
- 11
- 8
- Customer Identifier
- E074775
In many cases, columns can simply be specified by (field) name when performing queries on SQL tables. This in turn allows easy access of these columns when processing the query results. Consider the following code:
In the code above, an SQL SELECT statement is used to get the first and last names of all customers living in New York. The desired columns are specified as field names to the SELECT statement and hence the values in the member variables of the resulting DataObject(s) can also be accessed by name. But what happens if an SQL expression such as Count(*) is specified in the [<ColumnExpression>,..] parameter to SQL SELECT?
Fact is, although the column count *is* returned as a member variable in the query result, it cannot be accessed using the send operator ( : ). The problem is that the originating SQL expression is no legal member variable name in Xbase++ syntax.
The solution to this problem is to define an alias for the column using [AS <cAliasName>]:
Xbase++:
SELECT firstname, lastname FROM customer WHERE city='New York' INTO OBJECTS aObjs
? aObjs[1]:firstname
? aObjs[1]:lastname
In the code above, an SQL SELECT statement is used to get the first and last names of all customers living in New York. The desired columns are specified as field names to the SELECT statement and hence the values in the member variables of the resulting DataObject(s) can also be accessed by name. But what happens if an SQL expression such as Count(*) is specified in the [<ColumnExpression>,..] parameter to SQL SELECT?
Xbase++:
SELECT Count(*) FROM customer WHERE city='New York' INTO OBJECTS aObjs
? aObjs[1]:count(*) // Illegal syntax for accessing a member variable!
Fact is, although the column count *is* returned as a member variable in the query result, it cannot be accessed using the send operator ( : ). The problem is that the originating SQL expression is no legal member variable name in Xbase++ syntax.
The solution to this problem is to define an alias for the column using [AS <cAliasName>]:
Xbase++:
SELECT Count(*) AS customercount FROM customer WHERE city='New York' INTO OBJECTS aObjs
? aObjs[1]:customercount // Access member variable created by column name alias
Note: Using an alias for accessing columns which are created from SQL expressions is generally considered a good practice, regardless of how the result set is represented! If the result set is returned in a work area, eg. from a query using Pass-Through SQL, a similar problem arises when accessing computed fields.
Last edited by a moderator: