Home » RDBMS Server » Server Utilities » SQL Loader question: How to use two WHEN clauses for a single table load
icon5.gif  SQL Loader question: How to use two WHEN clauses for a single table load [message #213867] Fri, 12 January 2007 10:26 Go to next message
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
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 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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
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 Go to previous messageGo to next message
bheemsen
Messages: 13
Registered: February 2006
Junior Member
I mean I have the rows like the followng.

123|k|test|test <--- should be loaded as WIRE_CNTR_CD <> 't'
345|t|test|test <--- should NOT be loaded as WIRE_CNTR_CD = 't'
345|k|test|m <--- should be loaded as LIVING_UNIT_ID <> 'c'
345|k|test|c <--- should NOT be loaded as LIVING_UNIT_ID = 'c'

-Bheem
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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
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 Go to previous messageGo to next message
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 '( )'.
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 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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
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 Go to previous messageGo to next message
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')
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 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
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
icon14.gif  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 Go to previous message
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
Previous Topic: problems with TimeStamp
Next Topic: SQL*Loader - file path / error opening the file (merged)
Goto Forum:
  


Current Time: Wed Dec 07 05:08:18 CST 2016

Total time taken to generate the page: 0.13565 seconds