SQL/MP Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-35
Developing Multistep Queries
4. Select the report information from the DEPTTEMP table:
>> SET LIST_COUNT 0;
>> SELECT DEPTNUM, SUM(RANGE1), SUM(RANGE2), SUM(RANGE3)
+> FROM DEPTTEMP
+> GROUP BY DEPTNUM;
S> DETAIL DEPTNUM,
+> COL 2 AS I12 HEADING "SAL. < 20000",
+> COL 3 AS I12 HEADING "SAL. < 50000",
+> COL 4 AS I12 HEADING "SAL. < 200000";
S> LIST ALL;
DEPTNUM SAL. < 20000 SAL. < 50000 SAL. < 200000
------- ------------ ------------ -------------
1000 1 3 5
1500 1 3 4
2000 0 4 5
. . . .
. . . .
4000 1 9 15
9000 0 1 2
--- 11 row(s) selected.
>>
By default, the range columns that receive no values from an INSERT command are
set to the system default value of zero; thus, they do not affect the final sums
computed for each column.
You can use this technique for other queries when you want to group rows and
compute aggregates based on different conditions.
Row Value as Percent of All Row Values
You can also use multiple step queries to compute what percent the current row value
is of all row values. For example, the following report displays the percent that an
individual's salary is of all salaries in a department. The report is produced with the
following steps:
1. Create a temporary table to contain the average salary for each department:
>> CREATE TABLE TEMPTABS.AVGTEMP (
+> DEPTNUM NUMERIC (4) UNSIGNED NOT NULL,
+> AVGSAL NUMERIC (6) UNSIGNED NOT NULL)
+> CATALOG TEMPTABS ;
2. Insert the department number and average salary into the temporary table:
>> INSERT INTO TEMPTABS.AVGTEMP
+> (SELECT DEPTNUM, AVG(SALARY) FROM PERSNL.EMPLOYEE
+> GROUP BY DEPTNUM);