OraFAQ Forum: Server Utilities » sqlldr WHEN clause using <=

Home » RDBMS Server » Server Utilities » sqlldr WHEN clause using <= (Oracle 11)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

sqlldr WHEN clause using <= [message #658486]

Thu, 15 December 2016 10:22

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?

Report message to a moderator

Re: sqlldr WHEN clause using <= [message #658493 is a reply to message #658486]

Thu, 15 December 2016 11:02

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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).

Report message to a moderator

Re: sqlldr WHEN clause using <= [message #658494 is a reply to message #658493]

Thu, 15 December 2016 11:19

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?

Report message to a moderator

Re: sqlldr WHEN clause using <= [message #658495 is a reply to message #658494]

Thu, 15 December 2016 11:26

Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...

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.

Report message to a moderator

Re: sqlldr WHEN clause using <= [message #658496 is a reply to message #658495]

Thu, 15 December 2016 12:51

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.

Report message to a moderator

Previous Topic:	expdp in windows batch file
Next Topic:	SQL loader problem when using sequence

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 03:03:36 CST 2024