Manualshelf

SQL/MX Report Writer Guide

ManualsBrandsHP ManualsServerHP NonStop G-Series
51525354555657585960
Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-2
Developing a Query
•
Preparing SELECT commands to be executed more than once
•
Creating views to simplify the specification of queries, save time, and make data
access and reporting easier for nontechnical users of the database
Developing a Query
Before you develop a query to select data, consider these questions:
•
What columns of information do you need and how should the columns be
arranged?
•
In what order should the rows appear?
•
What subtotals and totals are to be calculated?
•
What information do you want to include in the titles and footings?
At this point, you do not have to consider the details of formatting the report. You can
enhance the format after you have generated the basic content.
After you know what information you want, compose a SELECT command to retrieve
the data. The SELECT command must retrieve all the data you need, including
information in columns, titles, headings, and footings.
Locating the Data
Figure 3-1 illustrates the general content of a report on suppliers and the parts they
supply. The italicized words date and time, and the numbers below the column
headings, are notes about what data is needed to produce the report.
First, you must determine which database tables contain the data. Figure 3-1 uses data
from the SUPPLIER, PARTSUPP, and PARTS tables. The report contains this data in
the numbered output line items:
1 SAMDBCAT.SALES.PARTS.PARTNUM
2 SAMDBCAT.SALES.PARTS.QTY_AVAILABLE
3 SAMDBCAT.INVENT.PARTSUPP.PARTCOST
4 SAMDBCAT.INVENT.PARTSUPP.PARTCOST * SAMDBCAT.SALES.PARTS.QTY_AVAILABLE
5 SAMDBCAT.SALES.PARTS.QTY_AVAILABLE * (SAMDBCAT.SALES.PARTS.PRICE -
PARTSUPP.PARTCOST)
Each column name in this list is qualified by the name of the table that contains the
column. You can use the INVOKE command to determine the names of columns in a
table.
The report title includes the date and the time when the report is produced. These
values are to be provided through the CURRENT_TIMESTAMP function.