Posted by Steve Bell (Cloud Sherpas) in Developer Community on May 1, 2016 2:13:38 PM

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

 

 

Some Techno-babble:

 

  • 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.

 

Caveats:

 

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.

_________________________________________________________________________

CLICK HERE TO CONTINUE READING THIS ARTICLE

 

Advertisements