Using Conditional Retrieval › Using Calculated Values in Predicates

Using Calculated Values in Predicates

You can use arithmetic expressions to calculate a value for a search condition. Use the following symbols for arithmetic operations:

Remember that multiplication and division are performed first, from left to right, and addition and subtraction second, from left to right. You can control the order in which operations are performed by using parentheses to enclose the operations you want performed first.

How it's done

As part of the salary review process, Human Resources needs to identify all jobs where the difference between the maximum and minimum salaries is greater than $10,000. To do this, use the JOB table and enter:

select job_id, job_title, min_rate,
       max_rate, max_rate - min_rate
       from job
       where max_rate - min_rate > 10000;

The result looks like this:

JOB_ID  JOB_TITLE                   MIN_RATE        MAX_RATE        (EXPR)
------  ---------                   --------        --------        ------
  8001  Vice President              90000.00       136000.00      46000.00
  2077  Purch Clerk                 17000.00        30000.00      13000.00
  9001  President                  111000.00       190000.00      79000.00
  4700  Purch Agnt                  33000.00        60000.00      27000.00
  3029  Computer Operator           25000.00        44000.00      19000.00
  6011  Manager - Acctng            59400.00       121000.00      61600.00
  4130  Benefits Analyst            35000.00        56000.00      21000.00
  4666  Sr Mechanic                 41000.00        91000.00      50000.00
  4123  Recruiter                   35000.00        56000.00      21000.00
  5555  Salesperson                 30000.00        79000.00      49000.00
  4025  Writer - Mktng              31000.00        50000.00      19000.00
  4023  Accountant                  44000.00       120000.00      76000.00
  4734  Mktng Admin                 25000.00        62000.00      37000.00
  5110  CUST SER MGR                40000.00       108000.00      68000.00
  6004  Manager - HR                66000.00       138000.00       72000.00
  5111  CUST SER REP                27000.00        54000.00       27000.00
  4012  Admin Asst                  21000.00        44000.00       23000.00
  2055  PAYROLL CLERK               17000.00        30000.00       13000.00
  5890  Appraisal Spec              45000.00        70000.00       25000.00
  3333  Sales Trainee               21600.00        39000.00       17400.00
  6021  Manager - Mktng             76000.00       150000.00       74000.00

21 rows processed