using MAx and group by in DB2

Question

I 'am Executing the Query below in DB2

select SOURCE1 , PLANT1,  max(BEGIN_TIMESTAMP) as dat , END_TIMESTAMP,
        TIMESTAMPDIFF(2,CAST((END_TIMESTAMP - BEGIN_TIMESTAMP) AS CHAR(22))) 
from tab1
group by dat

however i am getting this error :

2:01:01 [SELECT - 0 row(s), 0.000 secs] [Error Code: -206, SQL State: 42703] DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=DAT, DRIVER=3.50.152 ... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec [0 successful, 0 warnings, 1 errors]

Any idea Please

Side note: (In part due to the relative interval returned by the timestamp subtraction,) TIMESTAMPDIFF returns an estimate. In particular, the value will be off once you have a months (at least 1 day) or years (5 or 6 days). — Clockwork-Muse

Gordon Linoff Gordon Linoff · Accepted Answer · 2016-03-01T12:13:56

You need to include all non-aggregated columns in the GROUP BY. It is unclear exactly what you want, but this should at least parse and run:

select SOURCE1, PLANT1,  max(BEGIN_TIMESTAMP) as dat , END_TIMESTAMP,
        TIMESTAMPDIFF(2, CAST((END_TIMESTAMP - BEGIN_TIMESTAMP) AS CHAR(22))) 
from tab1
group by SOURCE1, PLANT1, END_TIMESTAMP;

using MAx and group by in DB2

1 Answers