SQL/MP Query Guide

Retrieving Data: How to Write Queries

HP NonStop SQL/MP Query Guide—524488-003

1-65

Developing Interactive Multistep Queries

This subsection describes techniques for developing multistep queries to select data

for reports. You can use multistep queries as follows:

•

To apply aggregate functions to multiple levels of groups

•

To compute what percent the current row value is of all rows

These techniques use temporary tables to select data based on more than one query.

To create a table, you use the CREATE TABLE statement. You must have access to a

catalog to define a table, or you must have the authority to create your own catalog.

For information about the requirements for creating tables, see the CREATE TABLE

statement in the SQL/MP Reference Manual.

For information about the report writer commands used in these examples (SET

LIST_COUNT, NAME, DETAIL, BREAK ON, LIST), see the SQL/MP Reference

Manual and the SQL/MP Report Writer Guide.

Multilevel Group Aggregates

The GROUP BY Clause on page 1-7 describes how to apply aggregate functions to

groups of rows. To apply aggregate functions to multiple levels of groups, you must

specify more than one query and use temporary tables.

For example, suppose that you want to report the average salary for each department

and, within each department, for each job classification. You must follow these steps:

1. Create a temporary table to contain the department number and average salary for

each department. Use the INVOKE statement to determine the data type for the

DEPTNUM column of the DEPT table. Then use CREATE TABLE to create the

temporary table:

>> CREATE TABLE DEPTAVG (

+> DEPTNUM NUMERIC (4) UNSIGNED NO DEFAULT,

+> AVGSAL NUMERIC (6) UNSIGNED NO DEFAULT )

+> CATALOG TEMPTABS ;

2. Insert the department number and average salary for each department in the

DEPTAVG table:

>> INSERT INTO DEPTAVG

+> (SELECT DEPTNUM, AVG(SALARY)

+> FROM PERSNL.EMPLOYEE

+> GROUP BY DEPTNUM) ;

3. Create another temporary table to contain the job code and average salary for

each type of job within a department: