>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?

The SOROUTE table is what the SOTRAN transactions are actually posted against. It is the actual route used for the Work Order, starting with the RODETAIL records as a template. Once a Work Order is created, it's local route (SOROUTE) can be customized for that Work Order. Scheduling info is actually in CPSCHED.

>
>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.

Not really sure what tags are available on your MASTER2 table. Have you used showplan (SYS(3054,11)) to actually see how your SELECT is optimized or not?

>
>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.
>

That would be great. It's always interesting to see how the tables are actually being used.

>__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.