SQL MODEL clause: First example

With this serie of posts, I want you show how to use SQL as spreadsheet.

For our first example, I consider the following query

SELECT
    employee_id,
    last_name,
    salary,
    commission_pct,
    department_id
FROM EMPLOYEES
WHERE (manager_id = 100 OR manager_id IS NULL)
ORDER BY department_id, employee_id;

 

 

This is how it is in a spreadsheet

 

What I want now is to calculate the "salary bonus" as

salary * commission_pct

The query is very simple

SELECT
    employee_id,
    last_name,
    salary,
    commission_pct,
    department_id,
    salary * commission_pct salary_bonus
FROM EMPLOYEES
WHERE (manager_id = 100 OR manager_id IS NULL)
ORDER BY department_id, employee_id;

and the output is

 

In this case, the spreadsheet is

In the formula, I used C2 and D2 columns. As you can see, Excel use 0 (zero) as result also when the commision_pct is NULL.

What about, I want to set the "salary_bonus" of King to SALARY*0,45?

 

The formula used in F14 cell is C14*0,45 (pay attention: here the decimal separator is the "comma", not the "full stop". This is because the Excel I'm using is configured to use the Italian language. The NLS_TERRITORY in the database is AMERICA, instead: I'm using AMERICAN_AMERICA.WE8MSWIN1252 as NLS_LANG on my laptop, so in SQL you can see the decimal separator as "full stop").

Now in SQL:

WITH
--------------
    bonus AS (
--------------
    SELECT
        employee_id,
        last_name,
        salary,
        commission_pct,
        department_id,
        salary * commission_pct salary_bonus
    FROM EMPLOYEES
    WHERE (manager_id = 100 OR manager_id IS NULL))
--------------
--------------
SELECT *
FROM bonus
MODEL
    DIMENSION BY (employee_id)
    MEASURES (
        last_name,
        salary,
        commission_pct,
        department_id,
        salary_bonus)
    RULES(
        salary_bonus[100] = salary[100] * .45)
ORDER BY department_id, employee_id;

 

 Some notes:

1. SALARY_BONUS is a calculated column, so you can't use it directly in the MODEL clause. For this reason, I used WITH
2. The columns in the result set are the one you specify in the DIMENSION BY and MEASURES (and PARTITION BY not used in this example)
3. The order of the columns positions depend on the order you specify them in the MEASURES
4. The values of MEASURES have to be unique, otherwise the ora-32638 is returned
5. You can use Live SQL, to run your test 
6. You can find all scripts of Oracle sample schema on GitHub