Using GUIDE in SQL Mode › Creating a Sample Query › Sample Procedure › Step 6: Select Columns for a Result Expression

Step 6: Select Columns for a Result Expression

This section tells how to select column names to include in an expression whose results appear in the output of the sample query.

After you select output columns and press <PF4> CONTINUE, CA Dataquery presents the following panel. It is the first of a series of panels that help you set up your own output columns to contain the results of expressions.

 =>

 --------------------------------------------------------------------------DQIA0
 DATAQUERY: DEFINE EXPRESSION: SELECT COLUMNS     QUERY NAME: SQLGUIDE
 -------------------------------------------------------------------------------
 SELECT ANY COLUMNS YOU WISH TO USE IN CALCULATING THE VALUE OF:
 EXPRESSION  01.
 PLACE ANY CHARACTER NEXT TO THE DESIRED COLUMN NAMES.
 -------------------------------------------------------------------------------
 SELECT|  TABLE NAME/COLUMN NAME              |  DESCRIPTION
 -------------------------------------------------------------------------------
       | CAI_SLSHST_TBL                       |
   _   |   ORD_ID                             |
   _   |   ORD_QTY                            |
   _   |   SHIP_QTY                           |
   _   |   UNIT_PRICE                         |
   _   |   DISC_PCT                           |
       |                                      |
       |                                      |
       |                                      |
 - LAST PAGE -------------------------------------------------------------------
 <PF1> HELP           <PF2> RETURN         <PF3> NOT USED   <PF4> CONTINUE
 <PF5> EXTEND COLUMN  <PF6> DISPLAY QUERY  <PF7> BACKWARD   <PF8> FORWARD

Facts

The following presents facts about selecting columns as part of an expression whose results are to be part of the sample query output.

Why does this panel appear?

You selected Create your own columns on the GUIDED QUERY CREATION OPTION SELECTION panel.

What does a result have to do with creating my own report columns?

To create your own data for the report, you must define an arithmetic expression or a literal value. The result is a value that appears on each output row in the output table. The expression is included in the SELECT clause.

What does an expression look like?

Example 1: (SALARY + BONUS) * .10

Example 2: 'RETIRED'

What do database columns have to do with creating my own columns?

Your query will contain an expression that results in a value that can be handled by CA Dataquery as though it were a value in a column. The expression can contain the names of columns in the table you select, so you get a chance to select those columns.

What happens to the columns I select?

CA Dataquery offers two panels for each expression needed. On this one, you select column names. To these names, CA Dataquery assigns numbers to represent them in the expression. On the second panel, you write the expression using the numbers to save space. (You also get a chance to do another expression.)

Steps

The following describes the procedure involved in selecting columns for the sample query expression.

Step 1

Determine what type of result you need to get.

Step 2

Determine the names of the columns that contain data you want used to create the values of one expression.

Step 3

Type an X in the fields preceding SHIP_QTY and UNIT_PRICE.

Step 4

Press <PF4> CONTINUE.

Options

The following lists some other things you can do during this step.

Extend Column

Display a new panel that provides background information about any column selected by positioning the cursor on it and pressing <PF5>.

Display Query

See how the query looks so far by pressing <PF6>. At this point, no columns or expressions follow the SELECT keyword. The SELECT clause is not complete until all Guided panels relating to it are completed.

Select more or fewer columns

Select as many columns as you need to include in an expression.

Select no columns

CA Dataquery displays the panel for writing an expression whether or not you select columns for it.

Additional Information

The following answers some other questions you might have about expressions that create your own columns on report output.

Will I encounter the expression as I use other GUIDE panels?

Yes. Any panels listing SELECT columns also list expressions by their names. The first expression you write receives the name EXPRESSION 01, and so on.

How do I recognize a result when it appears on reports?

The column heading is an automatically assigned expression name (like Expression 01) unless you assign a different name in the report format definition during execution.