19.2.2 Identifying Automation Rows to Process

Use an automation source query to choose lower-paid employees and prepare row values for actions.

The Saturday Employee Lottery automation identifies the rows to process using a source query. Since lottery candidates are lower-earning employees, it determines the salary cutoff for the bottom 25% of earners with the continuous percentile analytic function PERCENTILE_CONT. As shown below, the query's cutoff common table expression computes the salary limit for the group, then references that SALARY_LIMIT in the WHERE clause to retrieve employees earning that amount or less. It selects key columns from the EBA_DEMO_EMP table that automation actions use while processing each employee the query returns. The query aliases the ROWNUM pseudo-column to RN so each row gets an ordinal number.

Tip:

The query adds two extra NULL columns, aliased to NEW_SALARY and NEW_COMMISSION. Actions can assign values to these columns as temporary storage, passing data to later actions in the sequence. These values exist only in memory while the row is processed and are always VARCHAR2 type.

with cutoff as (
  select percentile_cont(0.25) 
         within group (order by sal) as salary_limit
  from   eba_demo_emp
)
select e.empno,
       e.ename,
       e.job,
       e.sal,
       e.comm,
       rownum as rn,
       nvl(:G_DEMO_EMAIL,'[e-mail]') as employee_email,
       null as new_salary,
       null as new_commission
from   eba_demo_emp e
join   cutoff c
  on   e.sal <= c.salary_limit

The figure shows the Source tab of the Automation edit page, showing the SQL statement above as the automation's data source.

Figure 19-2 Saturday Employee Lottery Automation Source Query

Description of "Figure 19-2 Saturday Employee Lottery Automation Source Query"