Re: SQLLDR and NULLs

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 Fri Apr 26 2002 - 17:35:22 CDT