Re: sql question
Obnoxio The Clown <obnoxio@hotmail.com> schrieb in im Newsbeitrag: 8330s6$39j$1@news.xmission.com...
>
> From: "Obnoxio The Clown" <obnoxio@hotmail.com>
> >
> >From: "Reinhard Habichtsberg" <Reinhard.Habichtsberg@unilux.de>
> >>
> >>Sorry Clown, don´t work!
> >
> >I don't?
You like to be my english teacher? Ok, I try to make it better:
Sorry Clown, it doesn´t work! Better? :-)
>
> Here's a conceptually similar sample using the stores7 database:
>
> select order_num, sum(quantity) hrosum
> from items
> where manu_code = "HRO"
> group by 1
> into temp t1;>
> select order_num, sum(quantity) anzsum
> from items
> where manu_code = "ANZ"
> group by 1
> into temp t2;>
> select items.order_num, hrosum, anzsum
> from items, outer t1, outer t2
> where items.order_num = t1.order_num
> and items.order_num = t2.order_num>
> Or if you wanted to be really flash:
>
> select order_num, sum(quantity) hrosum
> from items
> where manu_code = "HRO"
> group by 1
> into temp t1;>
> select order_num, sum(quantity) anzsum
> from items
> where manu_code = "ANZ"
> group by 1
> into temp t2;>
> select items.order_num, nvl(hrosum, 0), nvl(anzsum, 0)
> from items, outer t1, outer t2
> where items.order_num = t1.order_num
> and items.order_num = t2.order_num>
> :-)
>
Without looking to the structure and content of stores7:
> select items.order_num, hrosum, anzsum
> from items, outer t1, outer t2
> where items.order_num = t1.order_num
> and items.order_num = t2.order_num
I tried your proposal again. In _my_ case it I got the expected resulting table
only with a magic word added: unique.
select _unique_ items.order_num, hrosum, anzsum
from items, outer t1, outer t2
where items.order_num = t1.order_num
and items.order_num = t2.order_num
In my case there are multiple identical values in table item.
BTW: My first proposal works much faster.
Have I been enough specific now? :-)
Reinhard