NOTE: ON APRIL 1, 2016 ACCENTURE COMPLETED THE ACQUISITION PROCESS OF CLOUDSHERPAS. AT THAT TIME THE CLOUDSHERPAS BLOG SITES WERE DOWNED FOREVER.
THIS IS THE RE-PUBLICATION OF STEVE BELL’S ARTICLE FROM July 14, 2014 ON THE CLOUDSHERPAS SERVICENOW SCRIPTING 101 BLOG.
Database Views are useful for filling an important gap in ServiceNow: joining two or more tables together. In a normal report, you can only select a single table, but Database Views allow you to pull together data from two or more tables into a single “table” to report against.
Given the importance of Database Views, I wanted to take the opportunity to share some observations I have either read about, stumbled across or just plain hit-and-miss created from scratch.
To start off, check out the introduction to Database Views in the wiki
- Constrain / Constraint – to limit the View
- Where Clause – references the Constraint part of a SQL query. For example:
SELECT * FROM cmdb_ci WHERE install_status = 1
The “*” means to retrieve all fields. This example returns all records from the cmdb_ci table where the install_status field has a value of one.
- Join – to bring together two related tables into a single View.
- Union – to bring together two not necessarily related tables into a single View.
- Alias or Variable Prefix – a short descriptor that identifies a table and is used to designate that table’s fields in a Where Clause.
- Null – Empty field. Note that this is not the same as an empty string, which has to be checked for separately.
- Operators – &&, ||, AND, OR, !=, = – Are used to chain constraints together. Note that && and AND mean the same, as do || and OR.
For all of the examples, I use the CMDB CI table structure. Of course you can use any database available for your views. Rather than showing how to use specific fields in the view output, I will demonstrate techniques for joining or union-ing multiple tables together.