The following sql script was used to import data from a simple csv file into oracle 9i database, in order to update scrap pricing. We have upgraded to oracle 11g, All the table names and spaces are the same name and place in the database but the routine does not work with 11 no matter how I change it. Below is the original sql that works with the 9i data base..
I would like to 1st have it work properly, 2nd have it only replace rows that exist in the CSV first column, and then if possible have it check the form of the CSV, meaning is there a valid commodity code in the first column and second are there 6 columns of prices on that row of the CSV.(SQL Below)>
--This script will change the values in Tier Pricing in Scrapware SV (by --Vincent Costanza)
CREATE OR REPLACE DIRECTORY xtern_Tierdata_dir AS 'Z:\EXCEL\PriceList\CSV\TierP';
REM ------Create External table to import Tier_Pricing.csv...
create table Tier_Pricing_xtern
( comm_id NUMBER(10,0),
default directory xtern_Tierdata_dir
records delimited by newline
fields terminated by ','
missing field values are null
comm_id, Dealer, CommVdr, T1, T2, T3, T4
REJECT LIMIT UNLIMITED;
REM -----Load External Table with CSV data...
select * from Tier_Pricing_xtern ;
REM Creat internal Temp Table...
CREATE TABLE Tier_P_Tmp AS SELECT * FROM TIER_PRICE;
ALTER TABLE Tier_P_Tmp
ADD ( DE NUMBER(15,5),
REM copy data from external table to temp int table...
FOR c IN (SELECT * FROM Tier_Pricing_xtern)
SET DE = c.Dealer,
Com = c.CommVdr,
T1 = c.T1,
T2 = c.T2,
T3 = c.T3,
T4 = c.T4
WHERE comod_id = c.comm_id;
REM Drop External Table...
Drop table Tier_Pricing_xtern;
REM change date in temp table....seperate... method
SET s_dt = TRUNC(SYSDATE), e_dt = ADD_MONTHS(TRUNC(SYSDATE),2);
rem Move and Add into Tier_Price_DTL
MERGE INTO Tier_Price_Dtl t1
USING (SELECT TPRC_ID, 'T1' AS TPDT_CD, t1 AS price FROM Tier_P_Tmp UNION ALL
SELECT TPRC_ID, 'T2' AS TPDT_CD, t2 AS price FROM Tier_P_Tmp UNION ALL
SELECT TPRC_ID, 'T3' AS TPDT_CD, t3 AS price FROM Tier_P_Tmp UNION ALL
SELECT TPRC_ID, 'T4' AS TPDT_CD, t4 AS price FROM Tier_P_Tmp UNION ALL
SELECT TPRC_ID, 'DE' AS TPDT_CD, DE AS price FROM Tier_P_Tmp UNION ALL
SELECT TPRC_ID, 'COM' AS TPDT_CD, com AS price FROM Tier_P_TMP) x
ON ( t1.TPRC_ID = x.TPRC_ID
AND t1.TPDT_CD = x.TPDT_CD)
WHEN MATCHED THEN
UPDATE set t1.price = x.price
WHEN NOT MATCHED THEN
INSERT (TPRC_ID, TPDT_CD, price)
VALUES (x.TPRC_ID, x.TPDT_CD, x.price);
REM --Update dates in Tier_Price the aniquatited history tracking table...
FOR c IN (SELECT * FROM Tier_P_Tmp)
SET S_DT = c.S_DT,
E_DT = C.E_DT
WHERE TPRC_ID = C.TPRC_ID;
”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.
-Mike Kapnisakis, Warner Bros
With your subscription - you'll gain access to our exclusive IT community of thousands of IT pros. You'll also be able to connect with highly specified Experts to get personalized solutions to your troubleshooting & research questions. It’s like crowd-sourced consulting.
We can't always guarantee that the perfect solution to your specific problem will be waiting for you. If you ask your own question - our Certified Experts will team up with you to help you get the answers you need.
Our certified Experts are CTOs, CISOs, and Technical Architects who answer questions, write articles, and produce videos on Experts Exchange. 99% of them have full time tech jobs - they volunteer their time to help other people in the technology industry learn and succeed.
We can't guarantee quick solutions - Experts Exchange isn't a help desk. We're a community of IT professionals committed to sharing knowledge. Our experts volunteer their time to help other people in the technology industry learn and succeed.