Administering Data › Relational Database Query Utility › hsql Calculations Example

hsql Calculations Example

This example addresses the question that managers often ask, “What is development status?” A simple metric that addresses this question is the number of packages in each state in a development project.

To generate this report, write and save the following SQL in a file named pkgdist.sql. This SQL queries for the number of packages in all states of a project named Rel 1 Project.

SELECT
	statename, COUNT(*) AS number_of_packages
FROM
	harpackage, harstate, harenvironment
WHERE
	harstate.stateobjid = harpackage.stateobjid AND
	harpackage.envobjid = harenvironment.envobjid AND
	harenvironment.environmentname = 'Rel 1 Project'
GROUP BY
	statename

The following hsql command is executed:

hsql -t -f pkgdist.sql -o pkgdist.out

The command produces the following tab-delimited output:

STATENAME	NUMBER_OF_PACKAGES
Assign STRs	20
Implementation Design	3
Design Completed	1
Deferred	0
Invalid 	5
Development	22
Development Completed	4
Test/QA	10
Release	77

Because the output is tab-delimited, you can easily format it in a table using a word processor.