Create device database table views
About device database views
Database (DB) Views are virtual tables. Instead of containing data, they contain queries that dynamically retrieve data from tables. When data is added or changed in those tables, views return the added or changed data.
Device database views are implemented in SQLite. DB views can do the following:
-
Represent a subset of data in a table.
-
Join multiple tables into a single virtual table. In SQLite, the following are not supported: RIGHT OUTER JOIN and FULL OUTER JOIN (except LEFT OUTER JOIN, which is supported).
-
Aggregate data and present the calculated result as part of the data. The aggregate functions that are supported are: SUM, AVG, MIN, MAX, COUNT, and DISTINCT.
-
Secure data, as users can be given access to parts of a table, instead of the complete table.
-
Return data formatted differently from that of the underlying table.
SQLite database views are read-only. The DELETE, INSERT, or UPDATE statement cannot be used on a view.
Note:The Download source element becomes even more useful if it is specified that it uses a DB view that has been defined for a local database table. The DB view allows for using aggregate functions (for example, SUM, AVG, DISTINCT) and joining tables. If you are using an aggregate statement, such as SUM, or more than a single aggregate statement, you may need to CAST the type into the statement. For example: Cast(SUM(EXAMPLE_COLUMN) AS INT)
Define a device database view
Database (DB) Views are virtual tables. Instead of containing data, they contain queries that dynamically retrieve data from tables.
To define a device database view, complete the following steps in Advanced Inventory Platform Manager.
-
Go to Develop > Device DB Tables.
-
If there is more than one environment, select the environment in which to work.
-
Select Add New DB View.
-
On the Device Database View page, for DB view ID, enter an identifier for the database view. The ID must start with a letter and contain only letters, numbers, and underscores.
-
Optional. For Description, enter a description for the database view. This field accepts alphanumeric and special characters.
-
For SQL query, enter a valid SQL query. The aggregate functions that are supported are: SUM, AVG, MIN, MAX, COUNT, and DISTINCT.Step InformationIf you are using an aggregate statement, such as SUM, or more than a single aggregate statement, you may need to CAST the type into the statement. For example: Cast(SUM(EXAMPLE_COLUMN) AS INT)
-
Select Save.Expected ResultResult: If the query entered is not valid, an error message appears when Save is selected. Revise the query and select Save again.
Loading...
There was a problem loading this topic