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-13
Developing a Query
When you compare columns of fixed-length character data types, the LIKE predicate
might be more restrictive than a comparison predicate because the trailing blanks are
significant. You should use comparison predicates to search for exact values.
If you cannot locate a value in a column of a variable-length character data type, it
might be because trailing blanks were included when the value was inserted in the
table. For example, a value of "5MB" will not be located by LIKE "%MB". Try including
a % at the end of the comparison value.
Specifying More Than One Condition
You can use the Boolean operators NOT, AND, and OR to select data that satisfies
more than one condition as shown:
>> SELECT PARTNUM, PARTDESC
+> FROM SALES.PARTS
+> WHERE QTY_AVAILABLE < 2500
+> AND PARTNUM BETWEEN 2000 AND 3000
+> OR PARTNUM > 6000 ;
The following is the order of evaluation from first to last:
Expressions within parentheses
NOT
AND
OR
The interpretation of these conditions is this: If the quantity available is less than 2500,
part numbers from 2000 through 3000 are selected. Part numbers greater than 6000
are selected regardless of the quantity available.
The effect of parentheses is illustrated by the following revision of these conditions:
>> SELECT PARTNUM, PARTDESC
+> FROM SALES.PARTS
+> WHERE QTY_AVAILABLE < 2500
+> AND ( PARTNUM BETWEEN 2000 AND 3000
+> OR PARTNUM > 6000 ) ;
The interpretation of these conditions is this: If the quantity available is less than 2500,
part numbers from 2000 through 3000 or greater than 6000 are selected.
If you want NOT to apply to more than one predicate, you must enclose the predicates
in parentheses as shown:
WHERE NOT (PARTNUM BETWEEN 2000 AND 3000
OR PARTNUM > 6000)
Note. Specifying a pattern beginning with percent (%) in a LIKE predicate can result in a scan of a
complete table. You should not use this type of pattern when executing a query online unless other
predicates in the query provide access paths through indexes or primary keys. If you need to use this
type of pattern, execute the query in batch mode when the system has fewer demands on its resources.