Inventory (VERY LARGE TABLE)
I have the bulk of the query (using group by to get calculations) combining Orders, Items, Items_CustomFields, and Locations. This report runs within 20-30 minutes depending on server load. However, whenever I try and add either Inventory or Vendors I get the following Error:
ORA-01652: unable to extend temp segment by 5394 in tablespace SYSTEM
I have no control over the Oracle database, I can only send passthrough queries to retrieve READONLY data. So giving more temp space on the server is out of the question.
So, I tried the following technique:
b.QTY_On_Hand FROM (SELECT ..... [the main query] ...) a, Inventory b
a.Item=b.Item and a.Revision=b.Revision and a.Location=b.Location
Order by a.Item;
Using this technique I receive the following error:
ORA-01722: invalid number
I assumed this was because of the * so I specifically typed out all of the a.FIELDNAMES from the main query. But that too did not work stating that there was an invalid field name. I double checked all the spellings of the field names and their respective tables, but none of them are incorrect.
Can anybody suggest a better way to join these LARGE tables, or to be able to join them at all in this case!!! Any help would be greatly appreciated.
The account that accesses the Oracle database has SYSTEM tablespace as the temporary tablespace which is never a good idea. Please ask the database administrator to assign a temporary tablespace other than SYSTEM which is large enough to take care of the sorting associated with the query.
Regarding the last problem - please paste the exact query and error message so that we can take a look at that. There is definitely something wrong on your side otherwise the query would not fail.