-
Table drop & create
I have a simple SQL script that drops a table and then creates is new. When I run it I get the following error:
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
Table dropped.
Table created.
CREATE TABLE TMS_MASTER.SFI_TMS_GBC_ITP NOLOGGING
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
I queried ALL_OBJECTS table and this table did not exist in any other schema. What could be the problem?
Here's the script in case you want to see it:
WHENEVER SQLERROR CONTINUE;
DROP TABLE TMS_MASTER.SFI_TMS_GBC_ITP;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
CREATE TABLE TMS_MASTER.SFI_TMS_GBC_ITP NOLOGGING
AS
SELECT sol.load_id load_id
, to_char(trunc(a.creation_date),'MM/DD/YYYY') inv_create_date
, i.INV_NBR
, sol.ORD_ID
, loc1.NAME ship_from
, loc2.NAME ship_to
, r.source_warehouse SRC_WHS
, r.source_location SRC_LOC
, r.destination_warehouse DEST_WHS
, r.destination_location DEST_LOC
, round((sum(sol.ord_line_weight)/l.weight) * ic.amount,2) AMOUNT
FROM ord@pnetwork o
, load@pnetwork l
, LOCATION@pnetwork loc1
, LOCATION@pnetwork loc2
, fp_invoice@pnetwork i
, fp_invoice_cost@pnetwork ic
, fp_shipment_ord_ln@pnetwork sol
, sis_master.cyc_date_future d
, ap_invoices_all@popm a
, sfi_xfer_routes@popm r
WHERE o.group_name = 'ITP'
AND o.source_id = loc1.ID
AND o.dest_id = loc2.ID
AND o.source_id = '01004886020' -- GREEN BAY CREAMERY PLANT LOCATION
AND o.id = sol.ord_id
AND sol.line_freight_key = '502100' -- ITP FREIGHT - BULK CHEESE
AND r.load_id = SUBSTR(sol.load_id,1,11)
AND l.bill = sol.LOAD_ID
AND ic.cost_qualifier = 8 -- 8 = TOTAL COST
AND i.INVOICE_ID = ic.INVOICE_ID
AND i.inv_bol = substr(a.description,23,12)
AND a.description like '%TMS%'
AND sol.load_id = i.INV_BOL
AND a.invoice_num = i.INV_NBR
AND trunc(a.creation_date) >= d.prev_prd_begin_greg -- this part grabs records where the creation date falls
AND trunc(a.creation_date) <= d.prev_prd_end_greg -- between the begin and end dates of the previous period,
AND trunc(d.cal_day_greg) = trunc(sysdate) -- based on the current date
GROUP BY sol.load_id, to_char(trunc(a.creation_date),'MM/DD/YYYY'), i.INV_NBR, sol.ORD_ID
, l.weight, ic.amount, loc1.NAME, loc2.NAME, r.source_warehouse, r.source_location
, r.destination_warehouse, r.destination_location;
/
exit;
-
You are creating the table twice here in your script.
Code:
WHENEVER SQLERROR CONTINUE;
DROP TABLE TMS_MASTER.SFI_TMS_GBC_ITP;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
CREATE TABLE TMS_MASTER.SFI_TMS_GBC_ITP NOLOGGING
AS
SELECT sol.load_id load_id
, to_char(trunc(a.creation_date),'MM/DD/YYYY') inv_create_date
, i.INV_NBR
, sol.ORD_ID
, loc1.NAME ship_from
, loc2.NAME ship_to
, r.source_warehouse SRC_WHS
, r.source_location SRC_LOC
, r.destination_warehouse DEST_WHS
, r.destination_location DEST_LOC
, round((sum(sol.ord_line_weight)/l.weight) * ic.amount,2) AMOUNT
FROM ord@pnetwork o
, load@pnetwork l
, LOCATION@pnetwork loc1
, LOCATION@pnetwork loc2
, fp_invoice@pnetwork i
, fp_invoice_cost@pnetwork ic
, fp_shipment_ord_ln@pnetwork sol
, sis_master.cyc_date_future d
, ap_invoices_all@popm a
, sfi_xfer_routes@popm r
WHERE o.group_name = 'ITP'
AND o.source_id = loc1.ID
AND o.dest_id = loc2.ID
AND o.source_id = '01004886020' -- GREEN BAY CREAMERY PLANT LOCATION
AND o.id = sol.ord_id
AND sol.line_freight_key = '502100' -- ITP FREIGHT - BULK CHEESE
AND r.load_id = SUBSTR(sol.load_id,1,11)
AND l.bill = sol.LOAD_ID
AND ic.cost_qualifier = 8 -- 8 = TOTAL COST
AND i.INVOICE_ID = ic.INVOICE_ID
AND i.inv_bol = substr(a.description,23,12)
AND a.description like '%TMS%'
AND sol.load_id = i.INV_BOL
AND a.invoice_num = i.INV_NBR
AND trunc(a.creation_date) >= d.prev_prd_begin_greg -- this part grabs records where the creation date falls
AND trunc(a.creation_date) <= d.prev_prd_end_greg -- between the begin and end dates of the previous period,
AND trunc(d.cal_day_greg) = trunc(sysdate) -- based on the current date
GROUP BY sol.load_id, to_char(trunc(a.creation_date),'MM/DD/YYYY'), i.INV_NBR, sol.ORD_ID
, l.weight, ic.amount, loc1.NAME, loc2.NAME, r.source_warehouse, r.source_location
, r.destination_warehouse, r.destination_location;
/ ------- take this out as this runs the create stmt again
exit;
-
-
I think you can also have the driving_site hint, since I can see lots of dblink
Cheers!
OraKid.
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
|