A WHERE clause tells CA Dataquery the criteria for selecting data for the query output. It can also specify relationships that join tables by listing conditions to be found in two or more tables. This section presents basic WHERE clause rules and usage and discusses creation of the WHERE clause of the sample query.
.. ================================ T O P ===================================== 01 SELECT REP_ID, ORD_AMT, NAME 02 FROM GLS_CUST_TABLE, GLS_ACCTS_TABLE 03 WHERE table1.column1 = table2.column2 and column3 > 0 04 ORDER BY column1 .. =========================== B O T T O M ==================================
Facts
The following provides basic information about the purpose of WHERE clauses and rules for their use. Additional rules exist for complex usage. See the CA Dataquery Reference Guide for details.
The purpose of a WHERE clause is to:
WHERE clauses always follow a FROM statement.
You can use multiple WHERE clauses and join them by using AND, OR, and parentheses to create logical expressions.
Follow the word WHERE with a space and a predicate. Use spaces between words and symbols in the predicate.
Basic predicates compare two values and consist of one value followed by a comparison operator and another value. The value can be a column name or an expression. (The second value can also be a SUBSELECT.) See the CA Dataquery Reference Guide for instructions on using this SUBSELECT. The result of the comparison is either true or false. When the result is true for a given row, that row is selected for the result table.
Types of basic predicates are:
Example:
WHERE table1.name-column = table2.name-column
AND (AMOUNT >= 100 OR AMOUNT <= 1000)
OR (CURRENT_DATE_YEAR < 11)
Comparison operators define the type of comparison to be made between one expression and another. Valid operators in a basic predicate of an SQL WHERE clause are:
Expressions may be simple or complex, using arithmetic operators (+ - / * =) and parentheses. Use parentheses to clarify which operations are to be performed first in a complex calculation (up to 5 levels of nested expressions).
You can use other types of predicates in a WHERE clause. See the CA Dataquery Reference Guide for more information about:
Steps
These are the steps for editing the WHERE clause in the sample query:
Move the cursor to the t in the word table1.column1. Press the EOF or use the Delete key to delete the rest of the line.
To specify that the CUST_ID column in both tables is to be used to join the tables and that only rows where the ORD_AMT is greater than $1,000.00 are to be selected, type these characters beginning at the cursor location:
GLS_CUST_TABLE.CUST_ID = GLS_ACCTS_TABLE.CUST_ID AND ORD_AMT > 1000.00
Results
The following is a sample of a portion of the panel showing how the SELECT statement 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 03 WHERE GLS_CUST_TABLE.CUST_ID = GLS_ACCTS_TABLE.CUST_ID AND ORD_AMT > 1000.00
Options
The following is a guide to other things you can do when you create your own queries.
You can join up to ten tables (or result tables) in your queries using the WHERE clause.
You can create more complex joins by using the UNION keyword to join results tables created by two or more SELECT statements. See the CA Dataquery Reference Guide.
Operators are available for creating very specific search conditions.
Special keywords like BETWEEN, IN, LIKE, and IS NULL are available for creating specialized search conditions.
Create complex search conditions using parentheses and AND and OR to write the logical expression.
For example:
SELECT * FROM PAY WHERE COLUMN =
(SELECT * FROM PER)
|
Copyright © 2014 CA.
All rights reserved.
|
|