oracle - sql or plsql
Posted on 2007-10-03
I have a table of accounts called accts. Now for each account in that table I need to get the shipment revenue for the last five years from a table called ship. But the table called ship does not have 5 years of data. So I need to go to teh corresponding history table.
How do i do this.
acct table has acct_no
ship table has account_no, ship_YYYYMM, revenue
ship_history table has account_no, ship_YYYYMM, revenue
when i ran the query against data for five years , I am getting the foloowing error:
ORA-12801: error signaled in parallel query server P009
ORA-01652: unable to extend temp segment by 64 in tablespace TEMP_MKV02
What does this mean?> I know that that is a lot of data...is that the problem? Also I am going to run this query just one time to get the shipment history for 5 years for all accounts that exist in the alliance table
SELECT fedex_acct_nbr,net_rev_amt, shp_dt_YYYYMM
FROM dom_trans d,
where d.fedex_acct_nbr = ali.expr_acct_nbr
SELECT fedex_acct_nbr, net_rev_amt, shp_dt_YYYYMM
FROM dom_trans_hist d,
WHERE shp_dt_YYYYMM <= to_char(ADD_MONTHS((trunc(sysdate)),-60),'YYYYMM')
and d.fedex_acct_nbr = ali.expr_acct_nbr
1. Now going forward I want to get the "new" shipment data every month ((from the shipment table is is aggregated at the monthly level) for accounts for which history has already been populated by the one time lod of 5 year history
2. for the new accounts(that got inserted into the alliance table new and did not exist in the table previously, I want to get 5 years shipment data
3. I want to run a clean up process that removes data that is greater than 5 years of history
Please help with me these 3 points above. I am right now changing the question in this forum..
Can you please assist with this?