4.5 Writing Custom Queries

SQL Views uses views to make data accessible for use in reports. Views are used for two reasons:

The SQL dialect for SQL Views is slightly different than standard SQL

The definition of a view causes the altered SQL syntax to be hidden from the third party reporting tool or java application.
The definition of a view causes the database metadata to report a view in the database catalog

You can query the tables to either use the results for a custom view or in a report; the query must use SQL Views. The following is a sample query:

SELECT 
  EL1.NAME AS "Service Name", 
  EL1.CLASSNAME AS "Service Type", 
  EL1.DNAME AS "Service Address", 
  RL.NAME AS "Relationship Name", 
  EL2.NAME AS "Component Name", 
  EL2.CLASSNAME AS "Component Type", 
  EL2.DNAME AS "Component Address"
FROM NEW formula.Elements('root=Elements') AS EL1,
   NEW formula.Elements() AS EL2,
    NEW formula.Relationships() AS RL
WHERE EL1.ID = RL.LEFTID
AND EL1.CLASSNAME = 'computer_workstation'
AND EL2.ID = RL.RIGHTID;
SELECT * 
FROM Enterprise_Elements;
SELECT 
  EL1.NAME AS "Service Name", 
  EL1.CLASSNAME AS "Service Type", 
  EL1.DNAME AS "Service Address", 
  RL.NAME AS "Relationship Name", 
  EL2.NAME AS "Component Name", 
  EL2.CLASSNAME AS "Component Type", 
  EL2.DNAME AS "Component Address"
FROM NEW formula.Elements('root=Elements') AS EL1,
   NEW formula.Elements() AS EL2,
     NEW formula.Relationships() AS RL
WHERE EL1.ID = RL.LEFTID
AND EL1.CLASSNAME = 'computer_workstation'
AND EL2.ID = RL.RIGHTID;
SELECT *
FROM NEW formula.Properties('root=Elements') AS properties;
SELECT *
FROM NEW formula.Relationships() AS RL
WHERE RL.NAME <> 'NAM';