Friday, June 28, 2019

ODBC > Sample query for retrieving the Customer Category field from Customers table

As seen from the Table of ODBC Views, users might assume that the column name of the 'Category' field of the Customer record is 'category_0'. However, this field is actually tied to the Status (script id = 'entitystatus') from the backend.

For the users to query the 'Customer Category' field, they must use the 'customer_type_id' field which returns the internal id of the 'Category'. User can then use this to join it with the 'Customer Types' table to get its corresponding label. Below is a sample query for retrieving the Customer Category field along with its label.

SELECT cu.customer_id AS [Customer ID], cu.name [Customer Name], ctype.name AS [Customer Category] FROM customer_types ctype, customers cu WHERE ctype.customer_type_id = cu.customer_type_id

No comments:

Post a Comment