-
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
-
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|