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, 
         FROM   product_dim) SELECT Count(prod_id), 
FROM   prod 
WHERE  prod_reg = ‘INDIA’ 
SELECT Count(prod_id), 
FROM   prod 
WHERE  prod_reg = ‘USA’ 

Multiple with clause syntax 

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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s