Macro

Building Your First SQL Macro

Suppose I want to have a simple way for my developers and users to view total sales revenue from the fact table SALES for a specific zip code in the CUSTOMERS table. In effect, we need to create a paramterized view using a table macro.

This requires a join for the two tables SALES and CUSTOMERS, then we need to find the matching rows for the zip code and finally sum the result. The table macro will look like this:

CREATE OR REPLACE FUNCTION total_sales(zip_code varchar2) return varchar2 SQL_MACRO is
BEGIN
  RETURN q'{
   SELECT cust.cust_postal_code as zip_code,
             SUM(amount_sold) as revenue
   FROM sh.customers cust, sh.sales s
   WHERE cust.cust_postal_code = total_sales.zip_code
   AND s.cust_id = cust.cust_id
   GROUP BY cust.cust_postal_code
   ORDER BY cust.cust_postal_code
  }';
END;

run the macro

SELECT * 
FROM total_sales(zip_code => '60332');

Extend the macro

CREATE OR REPLACE FUNCTION Total_Sales(country VARCHAR2 default null,                                                            region  VARCHAR2 default null) 
                   RETURN clob SQL_MACRO is
BEGIN
  RETURN q'{
     SELECT 
            r.country_name            name, 
            r.country_region          region, 
            ROUND(SUM(s.amount_sold)) total_sales
     FROM sh. countries r, sh.customers c, sh.sales s
     WHERE r.country_id = c.country_id
     AND c.cust_id = s.cust_id
     AND r.country_name   = NVL(INITCAP(Total_Sales.country), r.country_name)
     AND r.country_region = NVL(INITCAP(Total_Sales.region),  r.country_region)
     GROUP BY r.country_id, r.country_name, r.country_region
  }';
END;

use the macro

SELECT 
 region, 
 name,
 total_sales,
 TRUNC(RATIO_TO_REPORT(total_sales) OVER (PARTITION BY region), 4) contribution
FROM (select * 
FROM total_sales(region => 'europe') 
ORDER BY 3 desc);

// select all without parameters


SELECT 
 region,
 name,
 total_sales,
 TRUNC(RATIO_TO_REPORT(total_sales) OVER (PARTITION BY region), 4) contribution
from (SELECT * 
FROM total_sales()
ORDER BY 1, 3 desc);