Re: SQLLOADER vs. NULLIF

I would say that your NULLIF should be more like NULLIF first1 = BLANKS. or whatever INFORMIX puts there to represent 'nodate'. Otherwise it will think valid date and attempt to convert it.

jeff.w.mcclure_at_ameritech.com wrote:
>
> Folks,
>
> I am attempting to transfer data from an Informix instance into an Oracle8
> (8.0.3.0.0) instance via SQLLOADER. My problem lies in transferring a
> date/time field. The output from Informix is in the format "YYYY-MM-DD
> HH:MI:SS" (there is a space between the date and time). My control file line
> looks like this:
>
> first1 date 'YYYY-MM-DD HH:MI:SS' NULLIF(first1 = X'4e554c4c')
>
> The error I am recieving is:
>
> Record 1: Rejected - Error on table ALERTLOG, column ACKNOWLEDGE.
> ORA-01849: hour must be between 1 and 12
>
> The date field (from Informix) may contain NULL. Where the field contains a
> date and time, it transfers correctly. How can I get this data to transfer
> when it includes a NULL date/time field (populating the Oracle8 instance with
> NULL in the date field)?
>
> Thanks,
>
> Jeff McClure
> Specialist DB Analyst
> Ameritech New Media
> Jeff.W.McClure_at_Ameritech.com
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/ Now offering spam-free web-based newsreading
  Received on Mon Mar 23 1998 - 00:00:00 CST