SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-6
Setting Criteria for Selecting Data
The select list must specify each column of data used in the report. For example, a
column value you print in a title or footing does not have to appear in the detail line, but
it must be retrieved in the select list.
You select specific columns by specifying column names. The select list for the
supplier parts summary consists of the column names in boldface type:
>> SELECT S.SUPPNUM,
+> SUPPNAME,
+> CITY,
+> STATE,
+> P.PARTNUM,
+> QTY_AVAILABLE,
+> PARTCOST,
+> PRICE
+> FROM SAMDBCAT.SALES.PARTS P,
+> SAMDBCAT.INVENT.PARTSUPP PS,
+> SAMDBCAT.INVENT.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:
>> 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.