SQL/MP Report Writer Guide
Table Of Contents
- What’s New in This Manual
- About This Manual
- 1 Introduction to the NonStop SQL/MP Report Writer
- 2 Using SQLCI and the Report Writer
- 3 Selecting Data for a Report
- 4 Customizing a Report
- Defining the Layout
- Specifying the Items in a Detail Line
- Naming Select List and Detail Line Items
- Organizing Rows Into Break Groups
- Labeling Information
- Formatting Data Values
- Formatting Dates and Times
- Using TACL to Pass Parameters
- Conditional Printing of Items or Line Entries
- Redefining Special Characters
- Calculating Totals
- Calculating Subtotals
- Printing Double-Byte Characters
- A Comparison of the Report Writer and the Enform Language
- Index
Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-8
Developing a Query
The following examples illustrate the effect of each predicate when included in the
following SELECT command:
>> VOLUME INVENT;
>> SELECT P.PARTNUM, QTY_AVAILABLE, PARTCOST, PRICE
+> FROM SALES.PARTS P, PARTSUPP PS, SUPPLIER S
+> WHERE P.PARTNUM = PS.PARTNUM
+> AND PS.SUPPNUM = S.SUPPNUM
+> ( Substitute predicate example from following text.)
•
The following comparison predicate specifies only suppliers identified by numbers
less than or equal to 400:
+> AND S.SUPPNUM <= 400;
•
Quantified predicates are useful for selecting rows based on their relation to all or
any rows selected by a separate search condition. For example, you can select
PARTSUPP table rows that contain a part cost value greater than the cost of every
part in the table supplied by supplier number 6 as shown:
+> AND PARTCOST > ALL ( SELECT PARTCOST FROM PARTSUPP
+> WHERE SUPPNUM = 6 ) ;
The subquery selects the part cost from each row describing parts supplied by supplier
number 6. The greatest part cost is $1100.00. The main query selects rows with a part
cost value greater than all values selected by the subquery; that is, rows with a part
cost greater than $1100.00.
IN Determines if a value is equal to any of the values in a list or in a collection
of values; for example:
PARTNUM IN (100, 120, 150)
(Part number must be 100, 120, or 150.)
LIKE Searches for strings to match a pattern that can contain wild-card
characters percent (%) and underscore (_) ; for example:
PARTDESC LIKE "DISK_T%"
(Part description contains DISK followed by exactly one character,
followed by T, followed by zero or more characters.)
EXISTS
Determines whether any rows satisfy the conditions of a subquery; for example:
SELECT * FROM ORDERS O
WHERE EXISTS (SELECT *
FROM ODETAIL OD
WHERE OD.ORDERNUM = O.ORDERNUM
AND PARTNUM = 244);
(Orders must include part number 244.)
NULL Determines whether a column contains a null value; for example:
UNIT_PRICE IS NULL
(Unit price must be null.)
Table 3-1. Search Condition Predicates (page 2 of 2)
Predicate Purpose