Retrieving Information from a Table › Retrieving Selected Rows › A simple comparison

A simple comparison

Defining a comparison expression

As stated above, the simplest comparison expression compares each value in a column to a constant. The constant can be either:

• Numeric (that is, decimal, integer, real, hexadecimal, binary, or multibit binary).
• Character. A character constant is enclosed in single quotation marks.

The symbols that compare one value to the other are:

If you prefer, you can also use these symbols: EQ, NE, GT, LT, GE, and LE.

Example 1— Comparing a number

List all the employees that work in department 4000:

select deptid, firstname, lastname
  from emp
  where deptid = 4000 ! display

                                   EMP REPORT
                                    mm/dd/yy

             DEPTID            FIRSTNAME                LASTNAME
             ──────            ──────────            ───────────────
               4000            MONTE                 BANK
               4000            MICHAEL               ANGELO
               4000            CAROL                 MCDOUGALL
               4000            LAURA                 PENMAN
               4000            JOCK                  JACKSON
               4000            BETSY                 ZEDI
     END OF REPORT

Example 2— Comparing a character

List the department and names of all female employees:

select deptid, firstname, lastname
  from emp
  where sex = &xq.f' ! display

                                   EMP REPORT
                                    mm/dd/yy

             DEPTID            FIRSTNAME                LASTNAME
             ──────            ──────────            ───────────────
               3100            JENNIFER              GARFIELD
               3100            JULIE                 JENSEN
               3100            JANE                  DOUGH
               6666            HENRIETTA             HENDON
               4000            CAROL                 MCDOUGALL
               4000            LAURA                 PENMAN
               4000            BETSY                 ZEDI
     END OF REPORT

Example 3— Selecting lower values

List the hire date, in ascending order, and names of all employees employed before January 1, 1978:

select startdate as &xq.hire date', firstname, lastname
  from emp
  where startdate < 780101
  order by startdate ! display

                                   EMP REPORT
                                    mm/dd/yy

            HIRE DATE           FIRSTNAME               LASTNAME
            ─────────           ──────────           ───────────────
               731121           HENRIETTA            HENDON
               750223           JOHN                 RUPEE
               760223           BETSY                ZEDI
               760808           JANE                 DOUGH
               770121           JENNIFER             GARFIELD
               770707           JOCK                 JACKSON
               770908           LAURA                PENMAN
     END OF REPORT

Example 4— Using a calculation

List all job classes where the salary range is less than $3,000:

select class, minsalary as &xq.minimum salary',
  maxsalary as &xq.maximum salary'
  from jobclass
  where (maxsalary - minsalary) < 3000 ! display

                                 JOBCLASS REPORT
                                    mm/dd/yy

            CLASS           MINIMUM SALARY           MAXIMUM SALARY
            ─────           ──────────────           ──────────────
               21                 18000.00                 20000.00
               33                 37000.00                 39000.00
               42                 33000.00                 35000.00
     END OF REPORT