Hello,
I have a requirement to load specific data to a table coming in a flat file where registration date is in the last 12 months. Below is the control file I have written.
Table Definition as below. The registration date comes in as yyyymm.
CREATE TABLE employee_registration
(EMP_NAME VARCHAR2(500),
DEPT_NO NUMERIC,
DEPT_TYP VARCHAR2(1),
REGISTRATION_DATE VARCHAR2(6),
REGISTRATION_TYPE VARCHAR2(100),
--THERE ARE 22 more columns
)
Since the registration date comes in as yyyymm I am using the below format to calculate the months between and the logic works in SQL.
Select registration_date, MONTHS_BETWEEN(SYSDATE, TO_DATE(registration_date||'01','yyyymmdd')) as mon_bet from employee_registration
201607 5.46553016726404
201508 16.465530167264
201511 13.465530167264
201606 6.46553016726404
201607 5.46553016726404
201607 5.46553016726404
201607 5.46553016726404
201606 6.46553016726404
OPTIONS (
ERRORS=5000,
DIRECT=TRUE,
ROWS=25000
)
LOAD DATA
INFILE '-' "str X'0A'"
TRUNCATE
INTO TABLE employee_registration
WHEN DEPT_TYP = 'P'
AND MONTHS_BETWEEN(SYSDATE, TO_DATE(REGISTRATION_DATE||'01','yyyymmdd')) >= 12
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
EMP_NAME,
DEPT_NO,
DEPT_TYP,
REGISTRATION_DATE,
REGISTRATION_TYPE,
...
...
)
When I execute the sqlldr it gives me the below error:
SQL*Loader-350: Syntax error at line 11.
Expecting "=", "<>" or "!=", found "(".
AND MONTHS_BETWEEN(SYSDATE, TO_DATE(REGISTRATION_DATE||'0
Can someone please help whats wrong here?
] 
Blog
Search
Help
Members
Register
Login
Home
