Home » RDBMS Server » Server Utilities » sqlldr WHEN clause using <= (Oracle 11)
sqlldr WHEN clause using <= [message #658486] Thu, 15 December 2016 10:22 Go to next message
Member2014
Messages: 33
Registered: July 2014
Location: USA
Member
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?
Re: sqlldr WHEN clause using <= [message #658493 is a reply to message #658486] Thu, 15 December 2016 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't think you can use functions there, the syntax diagram is:
[(] 
  {full_fieldname | pos_spec } operator { 'char_string' | X'hex_string' | BLANKS} 
  [)] AND
  [(]
  {full_fieldname | pos_spec } operator { 'char_string' | X'hex_string' | BLANKS}
  [)]...
So before the operator you can have only a field name or position (and after it only a constant).
Re: sqlldr WHEN clause using <= [message #658494 is a reply to message #658493] Thu, 15 December 2016 11:19 Go to previous messageGo to next message
Member2014
Messages: 33
Registered: July 2014
Location: USA
Member
Isn't that for files coming in with fixed positions? This is a pipe delimited file we are loading. I know we can have multiple conditions like WHEN value1 = a OR/ AND value2 = b. I'm not sure how this req can be implemeted.
Any other suggestions?
Re: sqlldr WHEN clause using <= [message #658495 is a reply to message #658494] Thu, 15 December 2016 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 65252
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is for any kind of file, you can have multiple conditions separated by AND or OR but you can't have function.
The problem is not the AND the problem is MONTH_BETWEEN, as the error message says, it find the "(" (of MONTH_BETWEEN) when it expected an operator (because it assumes that MONTH_BETWEEN is a field name).
In addition, it says it expects only "=", "<>" and "!=", so you can't have ">=".

To do what you want either:
1/ use a stage table where you load all rows and from which the insert only the wanted rows
2/ use an external table.

Re: sqlldr WHEN clause using <= [message #658496 is a reply to message #658495] Thu, 15 December 2016 12:51 Go to previous message
Member2014
Messages: 33
Registered: July 2014
Location: USA
Member
Aah, got it. I had read that we have to use only "=", "<>" and "!=" but was not sure if there is a work around for such conditions. Thanks for providing details. This is helpful.
Previous Topic: expdp in windows batch file
Next Topic: SQL loader problem when using sequence
Goto Forum:
  


Current Time: Thu Dec 14 07:58:26 CST 2017

Total time taken to generate the page: 0.01836 seconds