A SELECT clause tells CA Dataquery the names of columns that contain data to be found by the query. This section presents basic SELECT clause rules and usage and discusses creation of the SELECT clause of the sample query.
....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+. .. ================================ T O P ===================================== 01 SELECT column1, column2, count(column3) 02 FROM table1, table2 03 WHERE table1.column1 = table2.column2 and column3 > 0 04 ORDER BY column1 .. =========================== B O T T O M ==================================
Facts
The following tables provide basic information about SELECT statements and SELECT clause definitions, and SELECT clause rules. Details, additional keywords, and formats are available in the CA Dataquery Reference Guide.
The following outlines general information about the keyword SELECT.
SQL queries always begin with a SELECT statement. The first clause of a SELECT statement is the SELECT clause. A SELECT statement can contain additional clauses as well, beginning with the keywords FROM, WHERE, GROUP BY, and ORDER BY.
A SELECT clause names the columns containing data that is to appear on the report.
The following lists basic rules about SELECT clauses.
Follow the word SELECT with a space and a column name. Add additional column names as needed, separated by commas.
Example:
SELECT column1, column2, column3, column4
It is not necessary to attach the table ID to a column name unless you have duplicate column names (in more than one table) and you only want to print one of them. To attach a table ID, type the table name, a period, and the column name as one word.
Example:
SELECT column1, column2, table2.column3, column4
If the query finds duplicate rows and you only need one of them in your result table, follow the word SELECT with the word DISTINCT before listing column names. A space must precede and follow DISTINCT.
Example:
SELECT DISTINCT column1, column2, column3
To explicitly state that all rows found should be included in the report, insert the word ALL between SELECT and the first column name. Precede and follow ALL with a space.
Example:
SELECT ALL column1, column2, column3
To select data from all columns in the referenced tables in the report, use an asterisk (*) instead of listing the column names.
Example:
SELECT *
The order in which you list columns in the SELECT clause determines the order of columns on the report. If selecting from more than one table (specified in a FROM clause), you can list columns without regard to their table names.
Steps
These are the steps for editing the SELECT clause to match the query:
Move the cursor to the c in the word column1. Press the EOF key or use the Delete key to delete the rest of the line.
To show where to find the data and to specify the order of its presentation in the report, type these characters beginning at the cursor location:
REP_ID, ORD_AMT, NAME
Results
Here is a portion of the panel showing how the SELECT clause looks now.
DESCRIPTION: SAMPLE BASIC QUERY__________________________________________ ....+....1....+....2....+....3....+....4....+....5....+....6....+....7....+. .. ================================ T O P ===================================== 01 SELECT REP_ID, ORD_AMT, NAME 02 FROM table1, table2
Options
The following is a guide to other things you can do when you create your own queries.
You can display a list of tables for your current authorization ID if the cursor is in the text area. You can also see the column names for any table listed. You can use the lists for information or you can copy names from them into the text area at the point of the cursor. See Displaying Database Names and Structures or the CA Dataquery Reference Guide for details.
When the CURRENT TABLE field contains a name, you can display information about the columns in that table as you create the query. There are two ways to get a name in the CURRENT TABLE field. You can enter it or you can place the cursor in the text entry area, press <PF6> LIST TABLES, and select a table name to be inserted in both the text entry area and the CURRENT TABLE field. See Displaying Database Names and Structures and the CA Dataquery Reference Guide for details.
If you change to another known authorization ID on your USER PROFILE panel (PROFILE command), you can display lists of tables and columns in other schemas.
You can request that CA Dataquery return the results of a mathematical function you specify for one or more numeric columns, rather than the actual data itself. You can get sums, averages, minimum values, maximum values, or a count of the number of values in any column in the result table.
You can write an expression consisting of column names and arithmetic expressions and include the expression as a column name in the SELECT clause. The result appears in the output as column data.
|
Copyright © 2014 CA.
All rights reserved.
|
|