Yes that does have the data I want? I didn't see that table in my initial search of entering the transactions and this one getting updated? Looks like a traffic cop to a work order.
Not that we use 10% of TIW, but is this the scheduling table?
When I substituted your idea into the existing mess I had there was no reall change in performance. 33 seconds to 36 seconds respctfully.
If I take that join out, go from 3 tables to 2, it's only 2 seconds. Thats good enough. But the half min is too much.
Currently I'm trying to get the proper syntax for getting a select statement to process a sub query, and then use that in another. This will be presented in SQL area.
Thanks. When I get it done I'll pass it back to you.
__Stephen
>>>I have not seen TIW for sometime - but as I recall the SO number is in the TIW header file. So once you have the SO # (I'd get that from straight SBT searches) I think all you would need would be TIW tables. Of course I was just thinking of getting the status of each line item on the SO. It appears that you are doing more that just getting where or what process a line item is in WIP. Am I right? BTW the select statement was very nice.
>>>
>>>John
>>
>>It's kind of like that.{grin} TIW is more straight forward {normalized} than SBT. When it's done I'll post the improved sql.
>>
>>__Stephen
>
>Stephen,
>Another thing I've noticed about your select is that your trying to use the master route detail file (RODETAIL) to obtain the operation work center. Shouldn't you actually be using the Work Order route (SOROUTE) to be getting the Work Center actually used on the transaction? If you do it that way, we already have compound index tags on the Work Order # and the operation #. You wouldn't need to add any additional tags, just be sure that the left side of your JOIN statements has the proper fields to match the existing tags key.
>
>FROM sotran LEFT OUTER JOIN soroute ON sotran.sono+STR(sotran.opno,3,0)+sotran.code+sotran.part_grade=soroute.sono+STR(soroute.opno,3)
>
>Works pretty fast for me.
