In this step, you see how to format your report, give it a title, format the output data in your report and to designate control break totals for numeric columns.
Concepts
These are the major concepts related to this step:
PRINT
Tells CA Dataquery to produce a report with data arranged in columnar format with one line per row. You can have only one PRINT verb in your query. If you do not want your report in columnar format, then you should use DISPLAY instead of PRINT. You cannot use both PRINT and DISPLAY in a single query. When you execute the query, you can override the columnar format by indicating your choice on the ONLINE EXECUTION panel.
DISPLAY
Tells CA Dataquery to produce a report with data displayed in pages, with one page per row, one line per column. You can have only one DISPLAY verb in your query. If you do not want your report in page per row format, then you should use PRINT. You cannot use both DISPLAY and PRINT in a single query. When you execute the query, you can override the list format.
TITLE1
Tells CA Dataquery that the information following this word is to be used as the first line of a title for the report. You can specify a second title line using TITLE2. These titles will be preceded by a title which was selected for your site when CA Dataquery was installed. The title options are valid only with the PRINT statement.
'Report-heading'
You can give your report a title which identifies the intent of the report, such as FILLED CUSTOMER ORDERS. The maximum length is 55 characters. You must enclose the report heading in apostrophes, both for TITLE1 and TITLE2. CA Dataquery centers the title at the top of your report.
FROM
Optional word you can use to clarify your PRINT or DISPLAY statement when your report includes columns from more than one table.
Table-name
Identifies the table from which the columns that follow it are extracted. If you search two or more tables in your query, you must specify which table contains the columns you are including in your PRINT or DISPLAY statement. You can include columns from all tables searched by your query, but you must precede the column name with the table name.
ROWS
Optional word you can use to clarify your PRINT or DISPLAY statement.
Column-Name
You must specify the name of each column for which you want output data included in the report. You can use columns from the tables searched by your query and temporary results created by your query. The order in which you specify columns in your PRINT or DISPLAY statement determines the order in which they appear in your report. If you specify the name of a column which is a repeating field, you must use subscripts that show which occurrence you want to include in the report. See Repeating Fields for more information on repeating fields and subscripts.
Totals
Tells CA Dataquery which numeric columns should be totaled and displayed each time a control break occurs or at the end of the report. To indicate that a numeric column is to be totaled, enclose the name within parentheses in your print statement. Parentheses around a PRINT statement column name tell CA Dataquery to produce totals each time a control break occurs. (Control breaks are columns enclosed in parentheses in the SORT statement.) Totals are placed under the appropriate column in the report.
CA Dataquery prints a legend in your report telling you for which column and control break value the total was accumulated. If you have not included a SORT statement or if you did not designate any Control Breaks in your SORT statement, CA Dataquery will only print totals at the end of the report.
PICTURE
Tells CA Dataquery that the information following this word will describe an alternate edit pattern for numeric data. You can use this option to arrange the appearance of numeric data in your report, such as eliminating leading zeros.
If you do not specify an edit pattern, numeric data is displayed according to a default edit pattern defined in CA Datacom Datadictionary. If there is no edit pattern defined in CA Datacom Datadictionary, CA Dataquery generates a default edit pattern.
For example, UNIT-PRICE is defined in the database with five digits to the left of the decimal point and two to the right. If the price for an item is $29.50, this will be displayed in the report as 00029.50. To eliminate the leading zeros in the report, you can use the PICTURE option to define an edit pattern for this column. You can also use the PICTURE option to format data from temporary results.
'Picture-clause'
Specifies the edit pattern you want for the column. This pattern must be enclosed in apostrophes. Each column in your report can have a different pattern if you like since there are several patterns you can use. For details on valid edit patterns, see the CA Dataquery Reference Guide. Your site may have defined default edit patterns in CA Datacom Datadictionary. Use the Display Text function to view a particular column's edit pattern (if there is one) or consult your CA Dataquery Administrator.
'Optional-heading'
Unless you specify otherwise, the heading for each column listed in your PRINT statement will be the CA Datacom Datadictionary alternate heading, if specified, or the name of the column. To make your report clearer or more attractive, you can define an alternate heading for each column in your report. CA Dataquery will use this heading instead of the CA Datacom Datadictionary heading when producing the report. You must enclose this heading in apostrophes. The maximum length is 32 characters for each of two lines. To define a two-line heading, separate the two lines with the heading separator character shown on your SYSTEM PROFILE panel, available through Topic Help. If your site has defined default headings in CA Datacom Datadictionary, use the Display Text function to view a particular column heading or consult your CA Dataquery Administrator.
Procedure
In the previous step, a SORT statement was added to the query, to tell CA Dataquery how to arrange the query output. Following is an example of how it looks now:
=> --------------------------------------------------------------------------DQD10 DATAQUERY: EDITOR CURRENT TABLE: CAI-DETAIL-TBL ------------------------------------------------------------------------------- NAME: USER-SAMPLE TYPE: QUERY STATUS: PRIVATE DESCRIPTION: TOTAL ORDERS PER CUSTOMER -- AVERAGE PRICE ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+. .. ================================ T O P ===================================== 01 FIND ALL CAI-DETAIL-TBL ROWS 02 WITH SHIP-QTY GT 0 03 SET SHIP-PRICE(7.2) = UNIT-PRICE * SHIP-QTY 04 SORT BY (CUST-ID) .. =========================== B O T T O M ================================== ------------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> DISPLAY COLUMNS <PF4> DISPLAY KEYS <PF5> DISPLAY ALL <PF6> LIST TABLES<PF7> BACKWARD <PF8> FORWARD <PF9> TEMPLATE <PF10> VALIDATE <PF11> RIGHT/LEFT <PF12>PROCESS MODE
Action
To display the query template, press <PF9> TEMPLATE.
At the end of Step 5, the cursor was left at the end of line 4. This shows that it is not necessary to insert a line in the CREATE MODE panel if you are going to use the query template to select a format for insertion in your query. Pressing <PF9> displays the QUERY TEMPLATE panel shown following:
=> Enter the number of times each command is to be included in the query --------------------------------------------------------------------------DQD30 DATAQUERY: QUERY TEMPLATE ------------------------------------------------------------------------------- Lower case words represent entries which must be replaced by the user. Parentheses indicate optional entries: these may be included by removing the parentheses only or deleted by removing both the parentheses and data. ------------------------------------------------------------------------------- _ FIND (count) table-name ROWS _ WITH selection-criteria _ RELATED BY key-name TO (FIRST) table-name _ RELATED BY link-column VIA key-name column-name TO table-name _ SET result-column (13.2) = numeric-column-name arithmetic-expression numeric-literal _ SORT table-name BY column-name (DOWN) ------------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> NOT USED <PF4> NOT USED <PF5> NOT USED <PF6> NOT USED <PF7> BACKWARD <PF8> FORWARD
Action
To scroll forward, press <PF8> FORWARD.
The format needed in the query is not displayed on the above panel, but pressing <PF8> scrolls forward. If your terminal displays more than 24 lines at a time, CA Dataquery will display all of QUERY TEMPLATE on one panel and you do not have to use <PF8> to scroll forward.
=> Enter the number of times each command is to be included in the query --------------------------------------------------------------------------DQD40 DATAQUERY: QUERY TEMPLATE ------------------------------------------------------------------------------- Lower case words represent entries which must be replaced by the user. Parentheses indicate optional entries: these may be included by removing the parentheses only or deleted by removing both the parentheses and data ------------------------------------------------------------------------------- _ PRINT FROM table-name ROWS column-name ('optional-heading') (PICTURE 'picture-clause') _ DISPLAY FROM table-name column-name ('optional-heading') (PICTURE 'picture-clause') _ WHEN named-control-break-object FINISHED BREAKS DO 'legend-for-display' function column-name (PICTURE 'picture-clause') ------------------------------------------------------------------------------- <PF1> HELP <PF2> RETURN <PF3> NOT USED <PF4> NOT USED <PF5> NOT USED <PF6> NOT USED <PF7> BACKWARD <PF8> FORWARD
|
Copyright © 2014 CA.
All rights reserved.
|
|