why does this query not work ?
Is there a reason this won't work in IDS10 ?
select remote_addr, count(distinct query_id) count from web_l_sentrylog
group by remote_addr
having count(distinct query_id) > 5
I get an sql syntax 201 for the last line. If I were to remove the 'distinct' from the last line it would work, but it doesn't seem to like the having, together with the distinct ?
Thanks,
Floyd
It also doesn't work in IDS 11.10, and it doesn't make any difference
if the "count" column alias is replaced with a non-reserved word.
I suggest you report this as a bug. In the interim, you could use a
temporary table:
select remote_addr, count(distinct query_id) number
from web_l_sentrylog
group by remote_addr
into temp temp_table with no log;
select *
from temp_table
where number > 5;
drop table temp_table;
Regards,
Doug Lawry
> "Floyd Wellershaus" <floyd@fwellers.com> wrote in message
> news:mailman.1329.1215626774.20610.informix-list@iiug.org...
>
> Is there a reason this won't work in IDS10 ?
>
> select remote_addr, count(distinct query_id) count from web_l_sentrylog
> group by remote_addr
> having count(distinct query_id) > 5>
> I get an sql syntax 201 for the last line. If I were to remove the
> 'distinct' from the last line it would work, but it doesn't seem to
> like the having, together with the distinct ?
>
> Thanks,
> Floyd