Netezza GROUP BY sub column converting from Sybase
<p>I encountered a problem while converting from Sybase to Netezza. In example ill simplify the SELECT.</p>

<p>While this Sybase select works: </p>

<pre><code>SELECT  t1.col1,
CONVERT(CHAR(20), ymd(year(t1.date_col ),month(t1.date_col ),1) ,112)id_date,
CONVERT(CHAR(20), ymd(year(t1.date_col ),month(t1.date_col ),1) ,112) + 10000 id_date2,
SUM(t1.summ_col) summ_col
FROM test_table t1
GROUP BY t1.col1, id_date, id_date2
</code></pre>

<p>The same concept on Netezza will say that <strong>date_col</strong> must be in GROUP BY or in aggragate function. Despite the fact that columns  <strong>id_date</strong> and <strong>id_date2</strong> are already grouped.</p>

<p>Netezza : </p>

<pre><code>SELECT  t1.col1,
TO_CHAR( ymd(year(t1.date_col ),month(t1.date_col ),1), 'YYYYMMDD') id_date,
TO_CHAR( ymd(year(t1.date_col ),month(t1.date_col ),1), 'YYYYMMDD') + 10000 id_date2,
SUM(t1.summ_col) summ_col
FROM test_table t1
GROUP BY t1.col1, id_date, id_date2
</code></pre>