Re: SQLLDR and NULLs

From: Jake <jmoni_at_mail.com>
Date: 26 Apr 2002 13:25:14 -0700
Message-ID: <a677a6a3.0204261225.2b81e45f@posting.google.com>

Richard:

Thanks for looking into the problem. These are the answers to your questions.
1: Yes all the rwos are been loaded since I am loading fixed formatted fields

2: Whitespaces, for those with no entries
3: 8.1.6.0.0
4: Windows 2000 Profesional
5: Copy of the control file

load data
infile *
replace
into table EXAMPLE
fields terminated by whitespace
(

TRANS_ID	POSITION (01:03) CHAR, 
RECORD_ID       POSITION (04:04) CHAR NULLIF RECORD_ID=BLANKS,
CLEARING        POSITION (05:08) CHAR,
ACCOUNT	        POSITION (09:18) CHAR, 
P_C        POSITION (19:19) CHAR,
SYMBOL          POSITION (20:25) CHAR,
EX_DATE     POSITION (26:27) "TO_DATE(:EX_DATE||:EX_MONTH||:EX_DAY,

'YYMMDD')",
EX_MONTH POSITION (28:29) CHAR,

EX_DAY      POSITION (87:88) CHAR,
L_S      POSITION (37:37) CHAR,

TYPE POSITION (38:38) CHAR,
NET POSITION (59:65) "DECODE(:L_S,'L',:NET,'S',:NET*(-1))",

PRICE     POSITION (39:48) ":PRICE/1000000",
CUSPE           POSITION (66:74) CHAR,
RECORD     POSITION (80:80) CHAR,

RAID_SYMBOL POSITION (81:86) CHAR
"decode(:raid_symbol,null,'NULL',:raid_symbol)", DECIMAL POSITION (89:97) ":DECIMAL/10000", SPICE POSITION (98:102) INTEGER EXTERNAL) BEGINDATA

123 03551004230427ZD     03080000000SO0000175000         
00000030019899G6     0T  PD 2000020000000100                     J

123 03551004340427GH     01090000000LO0000025000         
00000060019989D3     0T  KD 2000020000000100                     J

123 03551004344122 TYW       0000000LS0034750000         
0050000879288308     0        00000000000000                     J

Thank you,

Jake

Richard Kuhler <noone_at_nowhere.com> wrote in message news:<DZYx8.56977$VQ2.34003215_at_twister.socal.rr.com>...
> 1. Are you sure the rows are being loaded?
> 2. What is the column actually filled with then?
> 3. What version of Oracle?
> 4. What OS version?
> 5. How about the whole control file?
> 6. How about a sample of the data file?
>
> Richard
>
> Jake wrote:
> >
> > Hello:
> >
> > I have loaded data into a table and one of the table columns is null
> > but when I run a SQL query against the the table I get zero rows
> > returned.
> >
> > ex. table Example has column D filled with nulls. When I run this
> > query
> >
> > select count(*) from Example where D is null; I get zero rows!
> >
> > I even had this in my control file:
> >
> > (1) D char nullif D=blanks, same results
> > (2) D char "decode(:D,null,null,:D), same resluts!
> >
> > Can anyone help me.
> >
> > Thanks in advance.
> > Jake
Received on Fri Apr 26 2002 - 15:25:14 CDT