Re: SQLLDR and NULLs

Richard,

Thanks again. This situation is very furstrating. RECORD_ID id defined as varchar2(1). I am not to worried about RECORD_ID, my concern is RAID_SYMBOL varchar2(6), which has some null entries but the select statement returns zero number of rows. I am inclining to think it a server problem. 'select ascii(record_id) from example' didn't help that much.

Jake

Richard Kuhler <noone_at_nowhere.com> wrote in message news:<KCky8.66550$zN.34410766_at_twister.socal.rr.com>...
> First off, I'm assuming we're talking about RECORD_ID. You didn't give
> the datatype of the column you are loading that into but I tried both
> varchar2(1) and char(1). With Oracle 8.1.7.2.0 and 9.0.1.3.0 I didn't
> have any problems loading that example data as null RECORD_ID.
>
> 15:20:50 > select count(*) from example where record_id is null;
>
> COUNT(*)
> ------------
> 3
>
> I'm really not sure what your problem could be. Possibly a problem with
> 8.1.6.0.0 but I seriously doubt it. Are you positive it's a blank
> that's being loaded? Maybe try 'select ascii(record_id) from example'
> to make sure.
>
>
> Richard
>
>
> Jake wrote:
> >
> > 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 Tue Apr 30 2002 - 10:09:02 CDT