Re: select last time for ordering - complex sql

From: jodleren <sonnich_at_hot.ee>
Date: Wed, 28 Apr 2010 06:56:25 -0700 (PDT)
Message-ID: <c59028c9-f95e-47e1-99ab-45f66331c8d4_at_c21g2000yqk.googlegroups.com>

On Apr 28, 12:29 pm, Carlos <miotromailcar..._at_netscape.net> wrote:
> On Apr 28, 10:48 am, jodleren <sonn..._at_hot.ee> wrote:
>
>
>
> > 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?
>
> (RECURSIVE SELECT) OUTER JOIN (ORDER/DELIVERY SELECT) ON PRODUCT ?
I can send you an email about it, then you can get a look at it. As of now, it does not work....

Note:

select product tree
select latest only data from table deliveries (if present) select order data from order table

this comes to:

SELECT level, stuff ,
 ordertable.orderdata, delirverytable.deliverydate FROM products
LEFT OUTER JOIN delirverytable ON
delirverytable.product=products.something

   AND delirverytable.deliverydate in (SELECT MAX(d2.deliverydate) delirverytable d2 where d2.product=products.something) CONNECT BY PRIOR products.subpart = products.product START WITH products = 'productname'

problem: I need the MAX in order to get only the latest from from delivery -
it works without the MAX, but then I get a long list of rows when it was delivered - btw it is sloooooow

Also, I need to get data from "product_details"

with the max I get this erorr:
[Oracle][ODBC][Ora]ORA-01799: a column may not be outer-joined to a subquery

---

next we need the order date:
this is not tested, and I am not even sure how to take the order table into it...

SELECT level, stuff ,
 ordertable.orderdata, delirverytable.deliverydate FROM products
LEFT OUTER JOIN delirverytable ON
delirverytable.product=products.something

   AND delirverytable.deliverydate in (SELECT MAX(d2.deliverydate) delirverytable d2 where d2.product=products.something) LEFT OUTER JOIN ordertable ON ordertable.orderno = delirverytable.orderno
CONNECT BY PRIOR products.subpart = products.product START WITH products = 'productname'

but I never get this far.

I wonder whether to do it in code - that might be both easier and faster

WBR
Sonnich Received on Wed Apr 28 2010 - 08:56:25 CDT