Thanks for noticing that mistake but when I took out the quotes there
was no difference i.e. decode(:raid_symbol,null,NULL,:raid_symbol), I
even tried this NULLIF raid_symbol=blanks no luck!
Richard Kuhler <noone_at_nowhere.com> wrote in message news:<mKBz8.68482$VQ2.40055887_at_twister.socal.rr.com>...
> Huh? RAID_SYMBOL has the expression ...
>
> "decode(:raid_symbol,null,'NULL',:raid_symbol)"
>
> So it won't ever have nulls in the database. Obviously, they should all
> be converted to the string 'NULL'. Or am I misunderstanding something?
>
> Richard
>
> Jake wrote:
> >
> > 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 Wed May 01 2002 - 11:41:58 CDT
