Home » Other » Client Tools » sql table does not exist error (SQL*PLUS, 10.2.0.2.0, windows XP)
sql table does not exist error [message #341329] |
Mon, 18 August 2008 06:56 |
josephsjrmurrell
Messages: 8 Registered: August 2008
|
Junior Member |
|
|
Hi all its the noob again.
I am using a batch file to create 5 sql table from 5 text files and then join all 5 tables into one. When i run the .bat file i get an error saying that the 5 individual tables required for joining does not exist. below are the code from the .sql, .ctl, .bat, files. each table has a control file and a sql file. i will just post the code from one each, since they are all structured the same way but just different field names...
CONTROL FILE CODE
load data
infile 'R:\CIS_LEGACY_FILES\BILLING.txt'
badfile 'bad_file_BILLING.log'
replace
into table legacy_cis.BILLING
fields terminated by "|"
(
BILL_KEY,
SERV_KEY,
SERVICE_NO,
CUSTOMER_NO,
CURR_DATE_TIME,
DATE_BILLED,
CURR_TIME,
CYCLE_NO,
TARIFF,
METER_NO_BILLED,
CUST_MULT,
READ_CODE,
PREVIOUS_DATE,
PREVIOUS_READING
)
SQL FILE CODE
DROP TABLE LEGACY_CIS.BILLING;
CREATE TABLE LEGACY_CIS.BILLING (
BILL_KEY VARCHAR2(30),
SERV_KEY VARCHAR2(14),
SERVICE_NO VARCHAR2(7),
CUSTOMER_NO VARCHAR2(7),
CURR_DATE_TIME VARCHAR2(16),
DATE_BILLED VARCHAR2(10),
CURR_TIME VARCHAR2(12),
CYCLE_NO VARCHAR2(3),
TARIFF VARCHAR2(2),
METER_NO_BILLED VARCHAR2(6),
CUST_MULT VARCHAR2(5),
READ_CODE VARCHAR2(2),
PREVIOUS_DATE VARCHAR2(10),
PREVIOUS_READING VARCHAR2(16))
nologging;
.BAT FILE CODE
call 00_setenv.bat
REM create initial tables
sqlplus LEGACY_CIS/VAX_CIS07@MTEST <R:\CIS_LEGACY_FILES\table_creation_scripts\BILLING.sql >logs\BILLING_tables.log
sqlplus LEGACY_CIS/VAX_CIS07@MTEST <R:\CIS_LEGACY_FILES\table_creation_scripts\BILLING_2.sql >>logs\BILLING_tables.log
sqlplus LEGACY_CIS/VAX_CIS07@MTEST <R:\CIS_LEGACY_FILES\table_creation_scripts\BILLING_3.sql >>logs\BILLING_tables.log
sqlplus LEGACY_CIS/VAX_CIS07@MTEST <R:\CIS_LEGACY_FILES\table_creation_scripts\BILLING_4.sql >>logs\BILLING_tables.log
sqlplus LEGACY_CIS/VAX_CIS07@MTEST <R:\CIS_LEGACY_FILES\table_creation_scripts\BILLING_5.sql >>logs\BILLING_tables.log
REM load BILLING.txt
SQLLDR userid=LEGACY_CIS/VAX_CIS07@MTEST Control=R:\CIS_LEGACY_FILES\SQL_LOADER\control\BILLING.ctl, LOG=logs\BILLING.log
REM load BILLING-2.txt
SQLLDR userid=LEGACY_CIS/VAX_CIS07@MTEST Control=R:\CIS_LEGACY_FILES\SQL_LOADER\control\BILLING_2.ctl, LOG=logs\BILLING_2.log
REM load BILLING-3.txt
SQLLDR userid=LEGACY_CIS/VAX_CIS07@MTEST Control=R:\CIS_LEGACY_FILES\SQL_LOADER\control\BILLING_3.ctl, LOG=logs\BILLING_3.log
REM load BILLING-4.txt
SQLLDR userid=LEGACY_CIS/VAX_CIS07@MTEST Control=R:\CIS_LEGACY_FILES\SQL_LOADER\control\BILLING_4.ctl, LOG=logs\BILLING_4.log
REM load BILLING-5.txt
SQLLDR userid=LEGACY_CIS/VAX_CIS07@MTEST Control=R:\CIS_LEGACY_FILES\SQL_LOADER\control\BILLING_5.ctl, LOG=logs\BILLING_5.log
REM Join tables and clean up
sqlplus LEGACY_CIS/VAX_CIS07@MTEST <R:\CIS_LEGACY_FILES\table_joining_scripts\BILLING_tab_join.sql >logs\BILLING_tab_sql_join.log
exit
SQL JOINING FILE CODE
CREATE TABLE LEGACY_CIS.BILLING_ALL
AS
SELECT
A.BILL_KEY,
A.SERV_KEY,
A.SERVICE_NO,
A.CUSTOMER_NO,
A.CURR_DATE_TIME,
A.DATE_BILLED,
A.CURR_TIME,
A.CYCLE_NO,
A.TARIFF,
A.METER_NO_BILLED,
A.CUST_MULT,
A.READ_CODE,
A.PREVIOUS_DATE,
A.PREVIOUS_READING,
B.PREV_COM_KWHS,
B.PREV_AVERAGE_KWHS,
B.PREV_DATE_BILLED,
B.PREV_KWHS,
B.PREV_DAYS_OF_SERVICE,
B.PREV_LAST_ACTUAL_DATE,
B.PREV_LAST_ACTUAL_READ,
B.READING_DATE,
B.READ_YRMO,
C.READING,
C.KWHS,
C.ENERGY,
C.FIXED_CHARGE,
C.FUEL_CHARGE,
C.VALUE_ADDED_TAX,
C.VALUE_ADDED_TAX_NET,
C.OTHER_CHARGES,
C.OTHER_CHARGES_CODE,
D.PERMANENT_OTH_CHGS,
D.ARREARS,
D.DEMAND_KVA_READ,
D.DEMAND_KVA_BILL,
D.PEAK_MARK_DATE,
D.MIN_BILL_CODE,
D.BLOCK_1_KWH,
D.BLOCK_2_KWH,
D.BLOCK_3_KWH,
E.DAYS_OF_SERVICE,
E.COM_KWHS,
E.BILL_PRINT_DIGIT,
E.TYPE_BILL,
E.CATEGORY,
E.BILL_FILL,
E.DISCOUNT_AMOUNT,
E.DATE_OF_STAT,
E.CURRENTDATE,
E.CURRENT_TIME,
E.OPERATOR,
E.CHANGE_TYPE,
E.PROGRAM_NAME
FROM BILLING A , BILLING_2 B , BILLING_3 C ,BILLING_4 D , BILLING_5 E
WHERE
A.BILL_KEY=B.BILL_KEY
AND B.BILL_KEY=C.BILL_KEY
AND C.BILL_KEY=D.BILL_KEY
AND D.BILL_KEY=E.BILL_KEY
AND A.SERV_KEY=B.SERV_KEY
AND B.SERV_KEY=C.SERV_KEY
AND C.SERV_KEY=D.SERV_KEY
AND D.SERV_KEY=E.SERV_KEY
AND A.SERVICE_NO=B.SERVICE_NO
AND B.SERVICE_NO=C.SERVICE_NO
AND C.SERVICE_NO=D.SERVICE_NO
AND D.SERVICE_NO=E.SERVICE_NO
AND A.CUSTOMER_NO=B.CUSTOMER_NO
AND B.CUSTOMER_NO=C.CUSTOMER_NO
AND C.CUSTOMER_NO=D.CUSTOMER_NO
AND D.CUSTOMER_NO=E.CUSTOMER_NO
AND A.DATE_BILLED=B.DATE_BILLED
AND B.DATE_BILLED=C.DATE_BILLED
AND C.DATE_BILLED=D.DATE_BILLED
AND D.DATE_BILLED=E.DATE_BILLED;
DROP TABLE BILLING;
DROP TABLE BILLING_2;
DROP TABLE BILLING_3;
DROP TABLE BILLING_4;
DROP TABLE BILLING_5;
RENAME BILLING_ALL TO BILLING;
is there something wrong with my table creation statement?
i used that statement structure to create 99 other tables and it worked, what am i doing wrong.
|
|
|
|
Goto Forum:
Current Time: Mon Aug 05 13:55:08 CDT 2024
|