My name is Todd Forsyth. I’m the Technical Lead at RSC LLC. We specialize in installing, hosting and managing ARCHIBUS CAFM/IWMS systems. This is one of a series of posts on how to make that process easier if you’re doing something similar yourself.
If you’ve done much ARCHIBUS customization, you know that the key is building great ARCHIBUS Views, or AXVW files. And so much of what makes this hard is getting the data that you need into the view. I’m going to talk about 3 SQL tricks I’ve learned to make this process easier.
Queries rather than tables
Many of the example queries you’ll see in ARCHIBUS views define datasources that connect directly to database tables. This is useful, of course, but sometimes you need data from multiple tables, which is very difficult, or even impossible, to write a datasource that does this by just referring to the tables directly.When this happens, it’s time to have the datasource refer to an SQL Query instead of to the tables themselves. This gives you the opportunity to develop the query in another tool (like SQL Server Management Studio), so you can be sure you have the data right, and then embed the Query in your ARCHIBUS View.
Here is an example from a custom Energy Management View RSC built recently. This was a BIG query. I haven’t include the whole query, or all the columns we really used in the datasource definition, but you get the idea:
<dataSource
id="ds_waterChart" applyVpaRestrictions="false">
<!-- Define parameters for the
custom SQL query -->
<parameter
name="locationField" dataType="verbatim"
value="ctry_id"/>
<parameter
name="locationValue" dataType="text"
value=""/>
<parameter
name="timePeriodFrom" dataType="text"
value=""/>
<parameter
name="timePeriodTo" dataType="text" value=""/>
<!-- Define a custom SQL query
that can be restricted by various location values -->
<sql
dialect="generic">select
'Water Usage
Comparison' water_usage_label,
'Water Cost
Comparison' water_cost_label,
sum(case
when
b.time_period like '2013-%'
then a.qty_volume * c.area_fraction
else 0
end) base_usage_hcf
from bill_line_archive a
join
bill_archive b
on a.bill_id
= b.bill_id
join
rsc_primary_building
c
on b.bl_id
= c.primary_bl_id
and a.meterseq = c.meterseq
join
bl d
on c.bl_id
= d.bl_id
join
vn e
on a.vn_id
= e.vn_id
where b.bill_type_id = 'WATER'
</sql>
<table
name="bill_archive" role="main"/>
<field
name="water_usage_label" dataType="text">
<title
translatable="true">Water Usage</title>
</field>
<field
name="water_cost_label" dataType="text">
<title
translatable="true">Water Cost</title>
</field>
<field
name="base_usage_hcf" dataType="number" size="12"
decimals="2">
<title
translatable="true">2013 Usage (HCF)</title>
</field>
</dataSource>
Use Unions to write a few small queries instead of one GIGANTIC query
Sometimes, writing even a query that can do EVERYTHING you need it to is frustratingly complex. It is often easier to write a series of SMALLER queries that can be tied together with a UNION operator, where each query does part of the work.For those not familiar with the SQL Union Clause, it is used to combine the result-set of two or more SELECT statements. Each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Additionally, the columns in each SELECT statement must be in the same order. As long as you follow these fairly simple rules, it’s easy to sew two queries together as though they were one, then embed the combined query in the SQL clause of an AXVW View.
A recent example was a view that was supposed to show the results of inspections, and to say whether these had passed, or failed. But “Pass” or “Fail” wasn’t a value stored in the database; I needed to do some logic to decide for each row.
Rather than writing one query, I wrote two. One that had logic to select all the “Pass” rows, and one to select all the “Fail” rows. Then I used the “UNION” clause to join them up. Much easier.
When Complexity is high, and so is repetition, use a Database View
I’m generally not a big fan of using Database Views in connection with the ARCHIBUS Application. For those not familiar with database views (as opposed to ARCHIBUS Views, which is just another name for an AXVW file) see this link. Basically an SQL View is a “virtual table,” a SQL query embedded in the database that queries can be written against. The problem with Database Views is that you have something outside the AXVW that it is dependent on (the SQL View). So any time you need to change the data that the AXVW is looking at, you need to change the SQL View. And if you move the AXVW from environment to another, you have to remember to bring the SQL View along, too.So when should you use one? Well, sometimes you’ll find yourself using NEARLY the SAME complex SQL Query in several AXVW files, or the SAME SQL Query multiple times in the SAME AXVW file. Changes to ALL these queries would be very time-consuming. So in the long term, it’s simpler to store all this oft-repeated SQL logic in one place. The SQL View.
Usually, a “.sql” text file containing the “CREATE VIEW” statement is made when you create the view. To make it easier to maintain the view over the long haul, and to migrate it between environment, it’s a good idea to keep this file around. If you don’t have a better system, it can be handy to add a comment to the AXVW that says “You need this view, too” and reference the text file used to create it. And to keep thr .sql script for generating the view close, consider storing it with a name similar to the AXVW view in the same folder where the AXVW file lives on your system. So if you view is “rsc_wr_update.axvw”, you might store the view-creating SQL script as “rsc_wr_update_vw.sql” in the same folder.
Like what you read? Subscribe to the blog and follow us on Twitter, Facebook, Linkedin and Pinterest to keep up to date with the latest news from RSC.
We hope this tips were useful! Comment below and let us know what you think! We'd love to hear your insights.
Follow @rsc2lc Tweet
Posts
