I have a 3 table join that takes 30+ seconds to run. Usetr puts in a PO # and the query will find all items on that po, and give the status for each line item.

Master2 has PO and WORK ORDER, part, etc.
SOTRAN has shop trans. (status in plant) can get up to 20+ per item
SOROUTE has the NAME for each location through the plant.

User will see What was ordered, and the last location. they can then tell a customer how long it will take to ship something.

Here is the mess:
SELECT lv_master2_shipping.partno, lv_master2_shipping.styledesc, ;
lv_master2_shipping.sono as sononum, lv_master2_shipping.lineno,;
lv_master2_shipping.wono, lv_master2_shipping.ponum , ;
Sotran.sono, Sotran.opno as opno, sotran.t_date ,;
soroute.loadcenter ;
FROM ves!lv_master2_shipping left outer join f:\tiwvan\sotran ;
LEFT OUTER JOIN soroute ;
ON sotran.sono+STR(sotran.opno,3,0)+sotran.code+sotran.part_grade =; soroute.sono+STR(soroute.opno,3);
on lv_master2_shipping.wono = sotran.sono ;
WHERE lv_master2_shipping.ponum= vp_ponum ;
group by 3,4 ;
into cursor query


What I would like to do is to have a select within a select.

I cannot get this right.

I just want to combine :
1) all transaction data with the NAME for that location,
2) that output with the sales data from the po (master2)

There is an easier join for #1 that what is above.

rodetail.opno = sotran.opno using RODETIL instead.

I have followed the concepts of querys where you look for parts not listed in teh lastphysical inventory.

SELECT lv_icitem_desc.item , lv_icitem_desc.itmclss ;
FROM lv_icitem_desc WHERE item NOT IN ;
(SELECT DISTINCT item_no as item FROM lv_inv_count) ;
order by 2,1 having val(itmclss) > 5



Any ideas on this one?

TIA!