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

On Apr 27, 5:49*pm, jodleren <sonn... (AT) hot (DOT) ee> wrote:
"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?

Cheers.

Carlos.

On Apr 28, 10:12*am, Carlos <miotromailcar... (AT) netscape (DOT) net> wrote:
But with 3 queries mixed?

That is where I get lost?

On Apr 28, 10:48*am, jodleren <sonn... (AT) hot (DOT) ee> wrote:
(RECURSIVE SELECT) OUTER JOIN (ORDER/DELIVERY SELECT) ON PRODUCT ?

Cheers.

Carlos.

On Apr 28, 12:29*pm, Carlos <miotromailcar... (AT) netscape (DOT) net> wrote:
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

On Apr 28, 3:56*pm, jodleren <sonn... (AT) hot (DOT) ee> wrote:
First of all, your second query (ordertable/delirverytable) is
uuuglyyy. Also, you likely would like to use some 'WITH'
refactoring...

But something like this maybe will do the homework OK

SELECT *
FROM ( SELECT LEVEL,
STUFF,
PRODUCT
FROM PRODUCTS
CONNECT BY PRIOR SUBPART = PRODUCT
START WITH PRODUCTS = 'productname' ) rec
LEFT OUTER JOIN ( select ordertable.orderdata,
delirverytable.deliverydate,
delirverytable.product
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)
) ord
ON (rec.PRODUCT = ord.PRODUCT)

Next time, ask the teacher ;-)

Cheers.

Carlos.