SQL Model Clause
bkd@Oracle>select employee_id,
2 first_name,
3 salary
4 from employees
5 where department_id=20;
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 /
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 /
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>/
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>/
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>/
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 /
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
