SQL Loader-NULLIF or WHEN ?

[drkestrel]

I have a SQL Loader control file as follows

--Load XE Initialisation data from datascope into instrument table
--Test NULLIF Clause in SQL Loader

Options (userid=test)


Load Data
infile 'test.dat'

REPLACE
-- load data wheather the table contain data or empty

Into table Test

(
CODE			POSITION(01:05)		CHAR WHEN(CODE NOT LIKE '% %')
)

I get error Expecting valid column specification, "," or &quot", found keyword when.
CODE POSITION(01:05) CHAR WHEN(CODE NOT LIKE '% %')

However,

CODE			POSITION(01:05)		CHAR NULLIF (CODE='   ')

works.

What i need the SQL Loader to do is to only import a record IFF code contains no spaces. Possible or not?

Neither WHEN nor NULLIF seems to like the tag

 LIKE '% %'

!

 

[karluk]

I think you have to move the when clause so that it's outside the parentheses that delimit the column list. Try the following

.
.
.
Into table Test
WHEN(CODE NOT LIKE '% %')
(
CODE POSITION(01:05) CHAR
)

 

[drkestrel]

I tried

........
Into table
WHEN (CODE<>-1) 
(
CODE    POSITION(01:05) CHAR
)

I received error Expecting quoted string or hex identifier, found "-"..

The field code is defined as NUMBER(3).

 

[karluk]

Clearly the problem is that CODE is CHAR rather than numeric, so when you say &quot;CODE<>-1&quot; you get an error. Oracle expects a quoted string like CODE <> '-1' instead of a number.

I think the solution at this point is up to you. I don't know the format of your data, so I can't guess what values should be selected for loading. You might be able to try something like

TO_NUMBER(CODE) <> -1

but this would probably generate an error if your data contains alpha characters.

 

[karluk]

Actually on reconsideration your problem may have a simple solution. If your database column is already defined as numeric, then you are probably just using the wrong format for your input file. Try something like

CODE POSITION(01:05) INTEGER EXTERNAL
or
CODE POSITION(01:05) DECIMAL EXTERNAL

depending on how the input file is formatted.

 

[drkestrel]

For your second resolution, if position 01:05 contains one ore more spaces?

 

[drkestrel]

For your second resolution, if position 01:05 contains one ore more spaces (now that I have changed CODE to be of type NUMBER)