Using Query Columns As Formula Cells in Oracle

The MODEL clause allows you to access a column in a row like a cell in an array. This gives you the ability to perform calculations in a similar manner to spreadsheet calculations.

The following query retrieves the sales amount for each month in 2014 made by employee id 21 for product types 1 and 2 and computes the predicted sales for January, February, and March of 2015 based on sales in 2014:

  SELECT prd_type_id,

         year,

         month,

         sales_amount

    FROM all_sales

   WHERE prd_type_id BETWEEN 1 AND 2 AND emp_id = 21

MODEL

   PARTITION BY (prd_type_id)

   DIMENSION BY (month, year)

   MEASURES (sales_amount)

      (sales_amount [1, 2015] = sales_amount[1, 2014],

      sales_amount [2, 2015] = sales_amount[2, 2014] + sales_amount[3, 2014],

      sales_amount [3, 2015] = ROUND (sales_amount[3, 2014] * 1.25, 2))




ORDER BY prd_type_id, year, month;

For the above query, below I am providing you the table script with an insert statement for testing this example:

 CREATE TABLE ALL_SALES

(

  PRD_TYPE_ID   NUMBER(10),

  YEAR          NUMBER,

  MONTH         NUMBER,

  SALES_AMOUNT  NUMBER,

  EMP_ID        NUMBER

);

SET DEFINE OFF;

Insert into ALL_SALES

   (PRD_TYPE_ID, YEAR, MONTH, SALES_AMOUNT, EMP_ID)

 Values

   (1, 2014, 1, 200, 21);

Insert into ALL_SALES

   (PRD_TYPE_ID, YEAR, MONTH, SALES_AMOUNT, EMP_ID)

 Values

   (1, 2014, 2, 250, 21);

Insert into ALL_SALES

   (PRD_TYPE_ID, YEAR, MONTH, SALES_AMOUNT, EMP_ID)

 Values

   (1, 2014, 3, 275, 21);

Insert into ALL_SALES

   (PRD_TYPE_ID, YEAR, MONTH, SALES_AMOUNT, EMP_ID)

 Values

   (2, 2014, 1, 301, 21);

Insert into ALL_SALES

   (PRD_TYPE_ID, YEAR, MONTH, SALES_AMOUNT, EMP_ID)

 Values

   (2, 2014, 2, 378, 21);

Insert into ALL_SALES

   (PRD_TYPE_ID, YEAR, MONTH, SALES_AMOUNT, EMP_ID)

 Values

   (2, 2014, 3, 490, 21);




COMMIT;