Retrieving Information from a Table › Retrieving Selected Rows › Complex comparisons

Complex comparisons

Using AND and OR

You can specify more than one comparison expression to select rows from a table by connecting each expression with an AND or an OR:

If a WHERE clause contains both AND and OR, CA OLQ evaluates the OR expressions first. For example, to process the example below, CA OLQ determines whether the employee's department is 4000 or whether the employee was hired before January 1, 1980. If either of these conditions is true, CA OLQ determines if the employee's job class is 21:

where class=21 and deptid=4000 or startdate<800101

Note: You can use parentheses to indicate the order in which CA OLQ evaluates the expressions. You can also use parentheses to improve the readability and accuracy of complex expressions.

Example 1— Using AND

List information about all employees who were hired before January 1, 1980 and whose salary exceeds $50,000:

select empid, lastname, startdate as &xq.hire date', salary
  from emp
  where (startdate < 800101)
    and (salary > 50000) ! display

                                   EMP REPORT
                                    mm/dd/yy

        EMPID          LASTNAME           HIRE DATE         SALARY
        ─────       ───────────────       ─────────       ───────────
         0001       RUPEE                    750223          76000.00
         0003       GARFIELD                 770121          65000.00
         0007       BANK                     780430          80000.00
         0030       HENDON                   731121         240000.00
         0471       PAPAZEUS                 780907          90000.00
         0472       WILDER                   790716          90000.00
     END OF REPORT

Example 2— Using parentheses with AND and OR

Parentheses determine how CA OLQ evaluates complex comparisons. Each of the SELECT statements shown below have the same three comparison expressions. However, the first SELECT statement uses parentheses to group the expressions connected by AND. The second groups the expressions connected by OR.

SELECT Statement 1

List the names, birthdays, and salaries of all employees who are either:

• Female, born before January 1, 1947 or
• Making less than $25,000

  select lastname, firstname, birthdate, salary
    from emp
    where (sex = &xq.f' and birthdate < 470101)
      or (salary < 25000)
    order by birthdate ! display
  

                                   EMP REPORT
                                    mm/dd/yy

          LASTNAME          FIRSTNAME       BIRTHDATE        SALARY
       ───────────────      ──────────      ─────────      ───────────
       HENDON               HENRIETTA          331006         240000.0
       ZEDI                 BETSY              401229         37000.00
       PENMAN               LAURA              440504         39000.00
       GARFIELD             JENNIFER           450818         65000.00
       TYRO                 RALPH              551225         20000.00
       ANGELO               MICHAEL            570405         18000.00
       MCDOUGALL            CAROL              590304         18000.00
     END OF REPORT

SELECT Statement 2

In comparison, list the same information about employees who:

• Are female and
• Earn less than $25,000 or were born before January 1, 1947

  select lastname, firstname, birthdate, salary
    from emp
    where (sex = &xq.f')
      and (birthdate < 470101 or salary < 25000)
    order by birthdate ! display
  

                                   EMP REPORT
                                    mm/dd/yy

          LASTNAME          FIRSTNAME       BIRTHDATE        SALARY
       ───────────────      ──────────      ─────────      ───────────
       HENDON               HENRIETTA          331006        240000.00
       ZEDI                 BETSY              401229         37000.00
       PENMAN               LAURA              440504         39000.00
       GARFIELD             JENNIFER           450818         65000.00
       MCDOUGALL            CAROL              590304         18000.00
     END OF REPORT