When you use the ODBC data provider, each entity in your repository has an associated query created for it, named for the entity and the entity’s unique reference/object ID number in the Control Center. Entity name and ID queries return only data and metadata specific to their associated entity.
When you run an entity_name(entity_ID) query without parameters, all entity data in columns in the Control Center is returned.
You modify these queries by specifying one or more of the following entity tables:
-
view_name=’view_attribute_null_values_rows’
-
view_name=’view_br_fail’
-
view_name=’view_entity_fail’
-
view_name=’view_rows‘
You can then add parameters to narrow the results within the entity table.
For example, for the entity Texas Data, if you want to return failing row data for an entity business rule with the reference (object) ID number of 2, run the following query:
SELECT * FROM "SCHEMA".OAUSER."Texas Data(469)" WHERE 'view_name=view_br_fail' AND "ebr"='2'To add parameters to a query
-
In Excel, select a cell in an open worksheet and click Data > Existing Connections. The Existing Connections window opens.
-
On the Connections tab, under Connection files on this computer, select the connection to the query you want to modify.
-
Click Open. The Import Data window opens.
-
Click Properties, then click the Definition tab in the Connection Properties window.
-
In the Command text field, edit the query with statement elements as needed.
SQL Statement Elements
Note: When you work with the queries, the term schema and the name of the query owner (for example, OAUSER) are a part of the syntax and must not be removed or changed. -
Click OK. A message displays stating that the original connection file has been modified.
-
In the Import Data window, click OK to run the query.
-
You are prompted to log on to the data source. Enter your password and click OK. The data referenced by the query opens in the worksheet.