Netezza GROUP BY sub column converting from Sybase

Question

I encountered a problem while converting from Sybase to Netezza. In example ill simplify the SELECT.

While this Sybase select works:

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

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

Netezza :

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

Answer 1

I guess this is what you want. Do the to_char stuff in a derived table. Do GROUP BY on it's result:

select col1, id_date, id_date2, SUM(summ_col) summ_col
from
(
    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,
           t1.summ_col
    FROM test_table t1
) dt
GROUP BY col1, id_date, id_date2