SQL Model Clause


bkd@Oracle>select employee_id,
  2               first_name,
  3               salary
  4        from employees
  5        where department_id=20;

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 Michael                   13000
        202 Pat                        6000

Using Model Clause,
bkd@Oracle>select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     dimension by (employee_id)
  8     measures( first_name, salary)
  9     rules()
 10  /

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 Michael                   13000
        202 Pat                        6000

bkd@Oracle>select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     dimension by (employee_id)
  8     measures( first_name, salary)
  9     rules(first_name[employee_id]='BKD')
 10  /

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 BKD                       13000
        202 BKD                        6000

bkd@Oracle>---aah!!!!
bkd@Oracle>ed
Wrote file afiedt.buf

  1  select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     dimension by (employee_id)
  8     measures( first_name, salary)
  9*    rules(first_name[9999]='BKD')
bkd@Oracle>/

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 Michael                   13000
        202 Pat                        6000
       9999 BKD

bkd@Oracle>ed
Wrote file afiedt.buf

  1  select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     dimension by (employee_id)
  8     measures( first_name, salary)
  9*    rules(first_name[9999]='BKD', salary[8888]=1425000)
bkd@Oracle>/

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 Michael                   13000
        202 Pat                        6000
       8888                         1425000
       9999 BKD

Here, we can see that if employee id is already exists then it overwrites the row.     

--creating new row with rule
bkd@Oracle>ed
Wrote file afiedt.buf
  1  select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     dimension by (employee_id)
  8     measures( first_name, salary)
  9*    rules(first_name[9999]='BKD', salary[9999]=1425000)
bkd@Oracle>/

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
        201 Michael                   13000
        202 Pat                        6000
       9999 BKD                     1425000

Use 'return updated rows'  to return only the new and updated ones:

bkd@Oracle>select  employee_id,
  2     first_name,
  3     salary
  4  from employees
  5  where department_id=20
  6  model
  7     return updated rows
  8     dimension by (employee_id)
  9     measures( first_name, salary)
 10     rules(first_name[9999]='BKD', salary[9999]=1425000)
 11  /

EMPLOYEE_ID FIRST_NAME               SALARY
----------- -------------------- ----------
       9999 BKD                     1425000

Now let's see what happened when we remove the filter on department and add the department_id to

measures.. 

bkd@Oracle>select  employee_id,
  2     first_name,
  3     salary,
  4     department_id
  5  from employees
  6  --where department_id=20
  7  model
  8     return updated rows
  9     dimension by (employee_id)
 10     measures( first_name, salary,department_id )
 11     rules(first_name[9999]='BKD', salary[9999]=1425000)
 12  order by department_id
 13  /

EMPLOYEE_ID FIRST_NAME               SALARY DEPARTMENT_ID
----------- -------------------- ---------- -------------
       9999 BKD                     1425000 

Using the department_id in partition by results...

bkd@Oracle>ed
Wrote file afiedt.buf

  1  select  employee_id,
  2          first_name,
  3          salary,
  4          department_id
  5  from employees
  6  --where department_id=20
  7  model
  8     return updated rows
  9     partition by (department_id)
 10     dimension by (employee_id)
 11     measures( first_name, salary )
 12     rules(first_name[9999]='BKD', salary[9999]=1425000)
 13* order by department_id
bkd@Oracle>/

EMPLOYEE_ID FIRST_NAME               SALARY DEPARTMENT_ID
----------- -------------------- ---------- -------------
       9999 BKD                     1425000            10
       9999 BKD                     1425000            20
       9999 BKD                     1425000            30
       9999 BKD                     1425000            40
       9999 BKD                     1425000            50
       9999 BKD                     1425000            60
       9999 BKD                     1425000            70
       9999 BKD                     1425000            80
       9999 BKD                     1425000            90
       9999 BKD                     1425000           100
       9999 BKD                     1425000           110

EMPLOYEE_ID FIRST_NAME               SALARY DEPARTMENT_ID
----------- -------------------- ---------- -------------
       9999 BKD                     1425000

12 rows selected. 

Regards,

BKD