Using GUIDE in SQL Mode › Creating a Sample Query › Sample Procedure › Step 8: Specify Column Order (and Functions)

Step 8: Specify Column Order (and Functions)

This section tells how to specify column order for the sample report produced by the sample query.

After you specify the output for your report (columns and result expressions), CA Dataquery displays this panel:

Output Row: Definition (DQII0)

 =>
  PRESS <PF1> FOR HELP WITH DEFINING THE OUTPUT ROW
 --------------------------------------------------------------------------DQII0
 DATAQUERY: OUTPUT ROW: DEFINITION             QUERY NAME:    SQLGUIDE
 -------------------------------------------------------------------------------
 SPECIFY THE ORDER IN WHICH THE COLUMNS ARE TO APPEAR IN THE OUTPUT ROW BY
 INSERTING AN ORDER NUMBER BELOW.  COLUMN FUNCTIONS MAY BE SELECTED BY PLACING
 ANY CHARACTER IN THE CORRESPONDING COLUMNS.  COUNT OUTPUT ROWS? _ YES X NO
 -------------------------------------------------------------------------------
 ORDER | TABLE NAME/                         |     |     |     |     | COUNT
 NUMBER|  COLUMN NAME                        | AVG | MAX | MIN | SUM | DISTINCT
 -------------------------------------------------------------------------------
       |CAI_SLSHST_TBL                       |     |     |     |     |
  01   | ITM_ID                              |     |     |     |     |
  02   | SHIP_QTY                            |     |     |     |     |
  03   | UNIT_PRICE                          |     |     |     |     |
       |USER-DEFINED EXPRESSION              |     |     |     |     |
  04   | EXPRESSION   01                     |     |     |     |     |
       |                                     |     |     |     |     |
       |                                     |     |     |     |     |
       |                                     |     |     |     |     |
 - LAST PAGE -------------------------------------------------------------------
 <PF1> HELP           <PF2> RETURN         <PF3> NOT USED   <PF4> CONTINUE
 <PF5> EXTEND COLUMN  <PF6> DISPLAY QUERY  <PF7> BACKWARD   <PF8> FORWARD

Facts

The following explains the facts needed to complete this panel for the sample query.

ORDER NUMBER

GUIDE assigns a default order on the report for output columns and results. You can change the order.

Steps

The following describes the steps required to complete the Output Row: Order column for the sample query.

Step 1

Decide whether the default order numbers show the way you want your report columns arranged.

Step 2

Accept the default by pressing <PF4> CONTINUE.

Options

Change the column order

Type over the numbers shown.

Apply column functions

Calculate results by performing mathematical functions on any or all numeric data.

Count rows in the output

Select Yes for COUNT OUTPUT ROWS?

Additional Facts

The following provides additional information about the OUTPUT ROW: DEFINITION panel (DQII0).

Why does this panel have columns headed with the names of mathematical functions?

You can also use this panel to apply column functions to any numeric column or result.

What happens when I select one of the functions?

This list shows the results of applying each function:

AVG

Returns the average value of a column.

MAX

Returns the highest value of a column.

MIN

Returns the lowest value of a column.

SUM

Returns the sum of all values in a column.

COUNT DISTINCT

If the query finds duplicate rows and you only need one of them in your result table, mark this function for columns.

Can I apply functions to some columns and not to others?

Yes. The result of applying column functions means that the report output will consist of column result rows and will not contain detail rows. Because of this, if you assign functions to some columns and not to others, those columns become the control breaks for the values that are produced.

You must decide the grouping priority for the unassigned columns. You do that on a new panel that appears for grouping. The grouping priority panel appears only if more than one column is unassigned because if there is only one, no priority decision is to be made.

How can I get detail rows in my output and also apply column functions?

You can omit column functions in the query definition and apply them later during report definition. The query output must be sorted on the panel discussed in Step 9: Specify Presentation Order.