Re: select last time for ordering - complex sql

From: jodleren <sonnich_at_hot.ee>
Date: Wed, 28 Apr 2010 01:48:40 -0700 (PDT)
Message-ID: <ca20a036-238f-4736-9913-c1009218d56e_at_11g2000yqr.googlegroups.com>

On Apr 28, 10:12 am, Carlos <miotromailcar..._at_netscape.net> wrote:
> On Apr 27, 5:49 pm, jodleren <sonn..._at_hot.ee> wrote:
>
>
>
> > Hi all
>
> > Basically I have this, which gives me a tree of the product and
> > subasseblies.
>
> > SELECT level, stuff from products
> > CONNECT BY PRIOR products.subpart = products.product
> > START WITH products = 'productname'
>
> > Now, they want to know delevery times for the last time when the parts
> > were ordered, this goes like:
>
> > select ordertable.orderdata, delirverytable.deliverydate
> > from ordertable, delirverytable
> > where ordertable.orderno = delirverytable.orderno
> > and delirverytable.product='Something'
> > and delivery date in (select max(delirverytable2.deliverydate) from
> > delirverytable2 where
> > delirverytable2.product = delirverytable.product)
>
> > basically - I get the latest date for delivery (if present) in order
> > to get one row only.
>
> > Now, can I mix all this together?
> > The point is, that some parts might never have been ordered, so they
> > are not in - at least one of - the order and delivery tables.
>
> > WBR
> > Sonncih
>
> "The point is, that some parts might never have been ordered, so they
> are not in - at least one of - the order and delivery tables."
>
> OUTER JOIN?
But with 3 queries mixed?

That is where I get lost? Received on Wed Apr 28 2010 - 03:48:40 CDT