OraFAQ Forum: Server Utilities » SQL Loader question: How to use two WHEN clauses for a single table load

Home » RDBMS Server » Server Utilities » SQL Loader question: How to use two WHEN clauses for a single table load

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

SQL Loader question: How to use two WHEN clauses for a single table load [message #213867]

Fri, 12 January 2007 10:26

bheemsen
Messages: 13
Registered: February 2006

Junior Member

Hi,

I want to use two WHEN conditions for a single table. When I tried the following, I got an error.

LOAD DATA
APPEND
INTO TABLE FF_LFACS_LIVING_UNIT
WHEN (WIRE_CNTR_CD <> 't')
WHEN (LIVING_UNIT_ID <> 'c')
FIELDS TERMINATED BY '|'
.....
....

I want to exclude the rows with WIRE_CNTR_CD = 't'
and rows with LIVING_UNIT_ID = 'c'

The error I got was

SQL*Loader: Release 9.2.0.7.0 - Production on Thu Jan 11 16:59:22 2007

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

SQL*Loader-350: Syntax error at line 8.
Expecting "(", found keyword when.
WHEN (LIVING_UNIT_ID <> 'c')
^

I do not know if the following solves my problem:

WHEN (WIRE_CNTR_CD <> 't') AND (LIVING_UNIT_ID <> 'c')

If I code like above, does the SQL Loader check for each condition in separate rows ?

Any help is really appreciated.

-Bheem

Report message to a moderator

Re: SQL Loader question: How to use two WHEN clauses for a single table load [message #213872 is a reply to message #213867]

Fri, 12 January 2007 10:34

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Quote:

WHEN (WIRE_CNTR_CD <> 't') AND (LIVING_UNIT_ID <> 'c')

If I code like above, does the SQL Loader check for each condition in separate rows ?

What do you mean by separate rows?
Anyway you can try that out and see on test environment.

By
Vamsi

Report message to a moderator

Re: SQL Loader question: How to use two WHEN clauses for a single table load [message #213877 is a reply to message #213872]

Fri, 12 January 2007 10:47

bheemsen
Messages: 13
Registered: February 2006

Junior Member

Report message to a moderator

Re: SQL Loader question: How to use two WHEN clauses for a single table load [message #213956 is a reply to message #213867]

Fri, 12 January 2007 23:55

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
you should try like this

INTO TABLE FF_LFACS_LIVING_UNIT
WHEN WIRE_CNTR_CD  != 't'
WHEN LIVING_UNIT_ID   != 'c'

regards
Taj

Report message to a moderator

Re: SQL Loader question: How to use two WHEN clauses for a single table load [message #213965 is a reply to message #213956]

Sat, 13 January 2007 01:07

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

@Taj, I don't think SQL Loader teats '<>' and '!=' differently.
@OP,SQL Loader
You should use as you mentioned in your previous post

Quote:

WHEN (WIRE_CNTR_CD <> 't') AND (LIVING_UNIT_ID <> 'c')

Do you have any problem to try that out and see on any test instance?

By
Vamsi

Report message to a moderator

Re: SQL Loader question: How to use two WHEN clauses for a single table load [message #213966 is a reply to message #213965]

Sat, 13 January 2007 01:11

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

Hi,
I don't think SQL Loader teats '<>' and '!=' differently.

they are some.

regards
taj

PS. in my previous post i was remove '( )'.

Report message to a moderator

Re: SQL Loader question: How to use two WHEN clauses for a single table load [message #213968 is a reply to message #213966]

Sat, 13 January 2007 01:45

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

My mistake. Just an overlook.

Anyway

Quote:

WHEN WIRE_CNTR_CD != 't'
WHEN LIVING_UNIT_ID != 'c'

will also not work.

WHEN WIRE_CNTR_CD  != 't'
AND LIVING_UNIT_ID   != 'c'

is working.
Seems we shouldn't use '(' in WHEN clause.

By
Vamsi

Report message to a moderator

Re: SQL Loader question: How to use two WHEN clauses for a single table load [message #213998 is a reply to message #213968]

Sat, 13 January 2007 08:02

ebrian
Messages: 2794
Registered: April 2006

Senior Member

vamsi kasina wrote on Sat, 13 January 2007 02:45

Seems we shouldn't use '(' in WHEN clause.

Vamsi, as you mentioned previously, the parenthesis are acceptable too.

WHEN (WIRE_CNTR_CD != 't') AND (LIVING_UNIT_ID != 'c')

Report message to a moderator

Re: SQL Loader question: How to use two WHEN clauses for a single table load [message #214109 is a reply to message #213998]

Mon, 15 January 2007 00:34

vamsi kasina
Messages: 2112
Registered: October 2003
Location: Cincinnati, OH

Senior Member

Both

Quote:

WHEN (WIRE_CNTR_CD != 't') AND (LIVING_UNIT_ID != 'c')
WHEN WIRE_CNTR_CD != 't' AND LIVING_UNIT_ID != 'c'

are working But

Quote:

WHEN ((WIRE_CNTR_CD != 't') AND (LIVING_UNIT_ID != 'c'))
WHEN (WIRE_CNTR_CD != 't' AND LIVING_UNIT_ID != 'c')

are not working.

By
Vamsi

Report message to a moderator

Re: SQL Loader question: How to use two WHEN clauses for a single table load [message #214258 is a reply to message #214109]

Mon, 15 January 2007 10:02

bheemsen
Messages: 13
Registered: February 2006

Junior Member

Thanks to all those who responded.

I want to let you know all that the following worked.

WHEN (WIRE_CNTR_CD <> 't') AND (LIVING_UNIT_ID <> 'c')

-Bheem

Report message to a moderator

Previous Topic:	problems with TimeStamp
Next Topic:	SQL*Loader - file path / error opening the file (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Dec 25 21:51:10 CST 2024