Params in are either the SO # or a PO # given by a customer on the phone. I have to pull the most recent status of each line item on the SO, from TIW and present it in a grid.

I tried to use SOMAST, SOTRAN, then tie to TIW data in their SOTRAN. It takes about 2 to 3 min.

I have a flat file {non normal } that combines somast and sotran data files from SBT. This takes about 2 min at best to get the same data. That table is ves!MASTER2

This is the best code so far:

CREATE SQL VIEW "LV_SOTRAN_max" ;
AS SELECT master2.styledesc, Sotran.sono, max(Sotran.opno) as opno, max(Sotran.t_date) as t_date *,;
Rodetail.loadcenter ;
FROM ves!master2 left outer join f:\tiwvan\sotran LEFT OUTER JOIN f:\tiwvan\rodetail ;
ON Sotran.opno = Rodetail.opno;
on master2.wono = sotran.sono ;
WHERE master2.ponum= vp_ponum ;
AND Rodetail.routeno = "222" ;
group by 2

Master2 was the flat file and sotran.sono is the work order # in TIW.

Anyone else done this?


f:\tiwvan\sotran may have 0 to 40 transactions per vp_wono. The max value tells me where it is last in the plant. Max date gives the data for that location.

The RODETAIL.loadcenter is just a descriptive tie for that location.

Any ideas ?