SQL/MP Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MP Report Writer Guide—527213-001
3-6
Developing a Query
You select specific columns by specifying the column names. The select list for the
supplier parts summary consists of the column names in boldface type that appear in
the following example:
>> SELECT S.SUPPNUM,
+> SUPPNAME,
+> CITY,
+> STATE,
+> P.PARTNUM,
+> QTY_AVAILABLE,
+> PARTCOST,
+> PRICE
+> FROM SALES.PARTS P, PARTSUPP PS, SUPPLIER S
+> WHERE P.PARTNUM = PS.PARTNUM
+> AND PS.SUPPNUM = S.SUPPNUM;
These are the only columns you need from the result of joining the three tables. You
must qualify SUPPNUM and PARTNUM because they appear twice in the initially
joined result table.
If you intend to use all the columns from a table, you can specify an asterisk (*) or an
asterisk qualified by the table name instead of specifying all the column names as
shown:
>> SELECT * FROM PARTS;
or
>> SELECT EMPLOYEE.*, JOBDESC
+> FROM EMPLOYEE, JOB
+> WHERE EMPLOYEE.JOBCODE = JOB.JOBCODE;
If you specify a DETAIL command, it determines the order of columns in report lines.
The order of the select list is important only when you do not specify a DETAIL
command.
By using the asterisk, you limit the options available to the query optimizer. You should
specify exactly the columns you need to provide more choices to the optimizer for
selecting a query plan.
Setting Criteria for Selecting Data
In addition to specifying how tables are joined, the WHERE clause specifies conditions
for selecting particular rows of data from the result table. The result table is the logical
table specified by the FROM clause, select list, and the WHERE clause itself, in the
case of joined tables.
For example, the following command selects only rows describing parts with at least 50
units available:
>> SELECT *
+> FROM SALES.PARTS
+> WHERE QTY_AVAILABLE >= 50;