A FROM clause tells CA Dataquery which table or tables to search to find the columns listed in the SELECT clause. This section presents basic FROM clause rules and uses and discusses creation of the FROM clause of the sample query.
.. ================================ T O P ===================================== 01 SELECT REP_ID, ORD_AMT, NAME 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 lists rules about basic FROM clauses. Details, additional keywords, and formats are available in the CA Dataquery Reference Guide.
A FROM clause names the tables containing data that is to be read in creating the report.
Follow the word FROM with a space and a table name. Add additional table names as needed, separated by commas.
Examples:
FROM table1, table2, table3, table4
FROM table1,table2,table3,table4
It is not necessary to attach the authorization ID to a table name unless it is not listed in the current schema. To attach an authorization ID, type it in front of the table name and separate with a period.
Example:
FROM table1, table2, public.table3, table4
The order in which you list tables in the FROM clause is of no importance.
You can list up to ten table names in one FROM clause. You can also use SQL to join and name result tables, or views. No more than ten tables can be referenced altogether, meaning your total count must include the number of tables making up a view.
Steps
These are the steps for editing the FROM clause to match the sample query:
Move the cursor to the t in the word table1. Press the EOF or use the Delete key to delete the rest of the line.
Type these characters beginning at the cursor location:
GLS_CUST_TABLE, GLS_ACCTS_TABLE
This tells CA Dataquery that the data to be printed in the report comes from the two tables named.
Results
The following is a sample of 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 GLS_CUST_TABLE, GLS_ACCTS_TABLE
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 to copy the table names you want right into the text area. All you do is position the cursor where you want the name and press <PF6> LIST TABLES. From the list that appears, you choose one or more table names. See Displaying Database Names and Structures or 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 in other schemas.
|
Copyright © 2014 CA.
All rights reserved.
|
|