Using GUIDE in SQL Mode › Creating a Sample Query › Sample Procedure › Step 7: Write an Expression that Creates Columns

Step 7: Write an Expression that Creates Columns

This section tells how to produce the result values in the sample business report.

After you select columns whose names you want to use in creating a result, press <PF4> CONTINUE. CA Dataquery displays the following panel so you can write the expression that produces output columns.

Define Expression: Write Statement (DQIB0)

 =>
  Enter definition for Expression 01.  Press <PF1> for help.
 --------------------------------------------------------------------------DQIB0
 DATAQUERY: DEFINE EXPRESSION: WRITE STATEMENT      QUERY NAME: _______________
 -------------------------------------------------------------------------------
 Write an arithmetic expression to be used in calculating the value of an
 output column.  Use the column numbers, F01, F02, etc., numeric literals,
 or a combination of both and arithmetic operators.
 The default expression appears below. You may type over it to change it.
 => F01 + F02__________________________________________________________________
 -------------------------------------------------------------------------------
 NUMBER|  TABLE NAME/COLUMN NAME              |  DESCRIPTION
 -------------------------------------------------------------------------------
       | CAI_SLSHST_TBL                       |
   F01 |   SHIP_QTY                           |
   F02 |   UNIT_PRICE                         |
       |                                      |
       |                                      |
       |                                      |
       |                                      |
       |                                      |
 -------------------------------------------------------------------------------
 <PF1> HELP           <PF2> RETURN         <PF3> NEW COLUMN  <PF4> CONTINUE
 <PF5> EXTEND COLUMN  <PF6> DISPLAY QUERY  <PF7> BACKWARD    <PF8> FORWARD

Facts

The following describes the facts needed to write the expression for the sample query.

Why does this panel appear?

Selecting Create your own columns on the GUIDED QUERY CREATION: OPTION SELECTION panel (DQHV0) gives you an opportunity to define an expression that produces results for the output. In the previous step, column names were selected for use in the expression. This is the panel for writing that expression.

Default Expression

CA Dataquery writes a default expression by adding assigned numbers together in a series. You can write over all or part of the default to change it.

F01

CA Dataquery assigns numbers for each column you select to be included in the expression. The numbers are the letter F followed by a number. The numbers are listed on the panel.

Length of expression

Your expression cannot exceed the length of the line containing the default expression.

Steps

The following describes the steps required to complete the expression for the sample query.

Step 1

Move the cursor to the + between F01 and F02 and type an asterisk over it so that the expression reads F01 * F02. The expression tells CA Dataquery to multiply the data in SHIP_QTY (F01) on each output row by the data in UNIT_PRICE (F02) on the same row to get a new value for that row. The new value will be included in the report to show the total price of each item listed on the report.

Step 2

Press <PF4> CONTINUE.

Options

Use another column name

Type the name of any column that fits on the line following the => entry field. You are not limited to only previously selected columns. However, if you misspell the name, CA Dataquery does not check it here.

Write any valid portion of a SELECT clause in the field

You could enter a comma-separated statement in the => entry field. You could also enter a column function. Column functions are discussed in Step 8: Specify Column Order (and Functions).

An example of a valid entry of this type is:

'Average Salary is: $', AVG(SALARY)

Add more columns with shorthand labels

You can use <PF2> RETURN to redisplay the panel for selecting expression columns and reselect columns.

To create a second result

Press <PF3> NEW COLUMN to display the panel for selecting columns to create another report column.

Additional Facts

The following answers some other questions you might have about expressions.

How do I get numeric results?

Either write out a number that is to appear on all output rows or write out an arithemetic expression that produces numeric results.

What should I know about numeric expressions?

The arithmetic expression can be made up of the names of numeric columns, arithmetic operators, parentheses, and numbers. Expressions may be simple or complex, using arithmetic operators (+ - / * =) and parentheses.

What is a nested expression?

Use parentheses to clarify which operations are to be performed first in a complex calculation (up to 5 levels of nested expressions).

Here is an example:

(SALARY + (SALARY * .10)) * .10

Notice that one set of parentheses is inside another one.

What is a complex expression?

Here is one:

(F01 * 3.3) + (F02/F05)/(F03 + F04)

Notice the expression consists of a combination of several simple expressions.

How do I get a literal value as the result?

Write a character string and enclose it in apostrophes.

Can I perform other kinds of calculations with Guide?

Yes. You can perform calculations on individual columns. For instance, you could calculate an average of the values in the UNIT_PRICE column by applying the AVG function to UNIT_PRICE. See Step 8: Specify Column Order (and Functions) or the CA Dataquery Reference Guide for more information.