Re: SQL delete based on aggregate function?

Loyd,
Man that's a real bummer.  That is not my understanding about how the
optimizer is supposed to work.
Don
Tully Consulting LLC

> Don,
>
> Running this query gives me a long runtime like the others with ...word
> in... syntax.
>
> Right now:
> Query running. 5 records selected, 3814089 processed.
>
> We're on V5R2. Group PTFs for SF99502 is level 12. I'll download the
latest
> (17) from IBM and see how that performs.
>
> Thanks,
> Loyd
>
> Loyd Goodbar
> Senior programmer/analyst
> BorgWarner
> E/TS Water Valley
> 662-473-5713
>
> -----Original Message-----
> From: Don Tully [mailto:dtully@xxxxxxxxxx]
> Sent: Thursday, January 20, 2005 11:30
> To: Midrange Systems Technical Discussion
> Subject: RE: SQL delete based on aggregate function?
>
> Loyd,
>
> That really surprises me.  I thought the optimizer was smarter than that.
> Maybe some IBM database gurus can answer why it does not.  What release
are
> you on?
>
> Try your first approach again.
>
> with temp as
> (select word, count(*) from wordfile2
>  group by word having count(*) = 1)
>
> Select count(*) from wordfile2 where word in
>   (select word from temp)
>
> Don
> Tully Consulting LLC
>