Michel Esber wrote:
> On 1 dez, 00:17, Serge Rielau <srie...@ca.ibm.com> wrote:
>> Maybe I'm missing something obvious....:
>>
>> SELECT ID, MAX(EXECUTION_DATE) FROM TABLEA GROUP BY ID
>> HAVING MAX(EXECUTION_DATE) < CURRENT DATE - 90 DAYS
>>
>> Cheers
>> Serge
>> --
>> Serge Rielau
>> DB2 Solutions Development
>> IBM Toronto Lab
>
> Hi Serge,
>
> In fact, this query does return the last execution prior to current
> date - 90 days, but it does not consider that IDs should not execute
> between current date - 90 days and current date
>
> The above query returns IDs that have and have not executed between
> current date - 90 days and current date.
>
> The query I have posted works fine. I am looking for a way to tune
> it ... Is there any way to produce the same result set with a single
> table scan ?
>
> TIA, Michel.

Serge's query works properly on my LUW FP14 system. I'd recommend trying
a small test case (a sample table containing three rows) to verify that
this is failing on your system. If it's failing, then it needs to be
reported. I strongly doubt that FP15 will break this simple SQL statement.

[psherman@T40 ~]$ db2 "create table t1 (id char(2) not null,
execution_date date not null with default)"
DB20000I The SQL command completed successfully.
[psherman@T40 ~]$ db2 "insert into t1 values ('A','2007-12-01')"
DB20000I The SQL command completed successfully.
[psherman@T40 ~]$ db2 "insert into t1 values ('A','2006-12-01')"
DB20000I The SQL command completed successfully.
[psherman@T40 ~]$ db2 "insert into t1 values ('B','2006-12-01')"
DB20000I The SQL command completed successfully.
[psherman@T40 ~]$ db2 "select id, max(execution_date) from t1 group by id"
ID 2
-- ----------
A 12/01/2007
B 12/01/2006

2 record(s) selected.

[psherman@T40 ~]$ db2 "select id, max(execution_date) from t1 group by
id having max(execution_date) < current_date - 90 days"

ID 2
-- ----------
B 12/01/2006

1 record(s) selected.


Phil Sherman