WITH Clause in Netezza

The use of WITH clause

• You can use WITH to write recursive queries
• You can use WITH to factor subqueries out of a main query.
• WITH clause allows you to create SQL queries that are friendly to read.
• It also allows you break down complex SQL queries into bite sized chunks that make it easy for debugging and processing the complex queries
• For performance.

Lets look at the syntax of WITH Clause in Netezza.

WITH prod (prod_id, prod_name, prod_reg) 
     AS (SELECT prod_id, 
                prod_name, 
                prod_reg 
         FROM   product_dim) SELECT Count(prod_id), 
       prod_name, 
       prod_reg 
FROM   prod 
WHERE  prod_reg = 'INDIA' 
UNION 
SELECT Count(prod_id), 
       prod_name, 
       prod_reg 
FROM   prod 
WHERE  prod_reg = 'USA' 

Multiple with clause syntax 

WITH simple_sum 
     AS (SELECT Trunc(calendar_date, 'mm') calendar_month_date, 
                claim_id, 
                SUM(amount_paid)           amount_paid 
         FROM   temp_claim_history 
         GROUP  BY Trunc(calendar_date, 'mm'), 
                   claim_id), 
     end_month 
     AS (SELECT Trunc(SYSDATE, 'mm') calendar_month_date 
         FROM   dual), 
     itd_sum 
     AS (SELECT simple_sum.calendar_month_date, 
                simple_sum.claim_id, 
                simple_sum.amount_paid, 
                SUM(simple_sum.amount_paid) 
                  over ( 
                    PARTITION BY simple_sum.claim_id 
                    ORDER BY simple_sum.calendar_month_date ) itd_amount_paid, 
                Nvl(Lead(simple_sum.calendar_month_date) 
                      over ( 
                        PARTITION BY simple_sum.claim_id 
                        ORDER BY simple_sum.calendar_month_date ), 
                Add_months( 
                end_month.calendar_month_date, 1)) 
                next_calendar_month_date 
         FROM   simple_sum, 
                end_month), 
     month_list 
     AS (SELECT Add_months(Trunc(To_date('2010', 'rrrr'), 'rrrr'), -ROWNUM + 1) 
                some_month_date 
         FROM   dual 
         CONNECT BY LEVEL <= 240), 
     dense_itd_summary 
     AS (SELECT itd_sum.*, 
                month_list.some_month_date filled_calendar_month_date 
         FROM   itd_sum, 
                month_list 
         WHERE  month_list.some_month_date >= itd_sum.calendar_month_date 
                AND month_list.some_month_date < 
                    itd_sum.next_calendar_month_date) 
SELECT filled_calendar_month_date, 
       claim_id, 
       itd_amount_paid 
FROM   dense_itd_summary 
ORDER  BY claim_id, 
          filled_calendar_month_date