Help Joining Large Tables

[aarians]

I have several large tables I need to pull data from using a passthrough SQL query in Access. From this I am going to populate an Access table for another application.

Here are my tables and their fields

Orders (VERY LARGE TABLE)
---------------------------------
Item
Revision
Location
Order Date
Quantity
etc.

Items (LARGE TABLE)
--------------------------------
Item
Revision
Description

Items_CustomFields
-------------------------
Item
Revision
CustomField
Vendor
Vendor_Location
(Used to determine if the Part is included in this particular report, and value must be in the resultset)

Locations
-----------
Location
CustomField (Yes/No)
(Determines if the location is included in the report)


Vendors
-----------------------
Vendor
Vendor_Loc
VendorName


Inventory (VERY LARGE TABLE)
--------------------
Item
Revision
Location
QTY_On_Hand


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:

SELECT
a.*,
b.QTY_On_Hand FROM (SELECT ..... [the main query] ...) a, Inventory b
WHERE
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.

Thanks in advance

[ssu4716]

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.

Thanks.

Syed