Re: grouping

On Jan 30, 1:02 pm, <gb> wrote:
> oracle 9
>
> I have a table with task_id , order_id , and status in
> My problem is I only want to get orders out that have all "COMP" or ALL
> "COMC" or all are "COM*"
> The sql works but is very very slow
> Any ideas please
>
> task id order id status
> 1 a COMP
> 2 a COMP
> 3 b COMP
> 4 c COMP
> 5 c COMC
> 6 c COMC
> 7 d COMP
> 8 d COMP
> 9 d ACT
> 10 d EXC
> 11 d ACT
> 12 e COMP
> 13 e COMC
> 14 e ACT
> 15 f COMP
> 16 f COMC
> 17 g ACT
>
> Sql problem
> select distinct t.order_id from tasks t,tasks t0
> where t.order_id = t0.order_id
> and t.STATUS = 'COMP'
> and t.order_id not in (
> select order_id from wm_tasks
> where STATUS NOt in ('COMP' ,'COMC')
> and order_id in (
> select distinct t.order_id from wm_tasks t,wm_tasks t0
> where t.order_id = t0.order_id
> and t.STATUS = 'COMP'));

Distincts tend to be killers, and not in's can be too, but you would be best off posting an explain plan so we can start to figure out what really is going on. Also post your exact version (ie, 9.2.0.7) and platform/os version, and any non-default init.ora parameters. See the oracle docs on the basics of how to read explain plans.

There's probably some OVER syntax that would make this better.

jg

--
@home.com is bogus.
http://www.oreillynet.com/onlamp/blog/2007/01/
survey_for_people_who_contribu.html