title: How to achieve CROSS APPLY in NETEZZA?
body: <p>I wanted to share how we can achieve <code>CROSS APPLY</code> functionality without using it.[works in <code>NETEZZA</code> <code>SQL</code>].<br></p>
  
  <p><strong><em>FOLLOWING QUERY USES CROSS APPLY TO RETRIVE LAST 52 WEEK FOF EACH WEEK:</em></strong> </p>
  
  <pre><code>SELECT      t1.col, 
              t3.col          AS col_last52wks
  FROM        TABLE1 t1
              CROSS APPLY (
                            SELECT TOP 52 t2.col
                            FROM  TABLE2 t2
                            WHERE t2.col &lt;= t1.col
                            ORDER BY t2.col DESC
                  ) t3
  --Last 13 Weeks Condition
  WHERE t1.col &gt;= minvalue -- RANDOM
  AND   t1.col &lt;= maxvalue-- RANDOM
  </code></pre>
  
  <p>We can achieve this with the following modifications in the above code and get rid of the <code>CROSS APPLY</code>:</p>
  
  <pre><code>SELECT  t1.col,
          t2.col             AS col_last52wks
  FROM    TABLE1 t1
          INNER JOIN TABLE2 t2
                  ON t2.sequencecol BETWEEN (t1.sequencecol - 51) AND t1.sequencecol
  WHERE t1.col &gt;= minvalue
  AND   t1.col &lt;= maxvalue
  </code></pre>