2 Replies Latest reply: Feb 11, 2013 3:48 AM by BluShadow RSS

    sqlldr date at when clause

    carmac
      Hi all,

      i need to create a conditional loader script in sqlldr.

      i write ctl script like this
      LOAD DATA
      INFILE 'COMSUMER.txt'
      BADFILE 'COMSUMER.bad'
      DISCARDFILE 'COMSUMER.dsc'
      APPEND
      INTO TABLE "RBS_CC_CUSTOMERINFO"
      WHEN CODE = '0' and PROC_DT < '01/01/2009'  
      FIELDS TERMINATED BY ','
      TRAILING NULLCOLS
      (
      PROC_DT  date "MM/DD/YYYY",
      ----
      -----
      ---
      )
      I got the error like this

      SQL*Loader-350: Syntax error at line 4.
      Illegal combination of non-alphanumeric characters WHEN PROC_DT < '01/01/2009' and CODE = '0'

      Please help me

      Edited by: carmac on Feb 11, 2013 2:56 PM

      Edited by: carmac on Feb 11, 2013 2:57 PM

      Edited by: BluShadow on 11-Feb-2013 09:38
      added {noformat}
      {noformat} tags for readability.  Please read {message:id=9360002} and learn to do this yourself.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
        • 1. Re: sqlldr date at when clause
          jeneesh
          If you can move the file to be loaded to the DB server, use External Table..It is more powerful..
          • 2. Re: sqlldr date at when clause
            BluShadow
            Ok, for starters:
            PROC_DT &lt; '01/01/2009'  
            when you compare DATE datatypes you should not use strings.

            In SQL or PL/SQL code such a date comparison would be something like:
            PROC_DT &lt; to_date('01/01/2009','DD/MM/YYYY')
            Personally, I'd consider using External Tables instead, and then apply your conditions on the SQL that queries the data from the External Table. Much more flexible and easy to do...

            http://www.morganslibrary.org/reference/externaltab.html