Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    66

    Unanswered: Sql Loader Problem

    Hi All,

    I need to load data into a table depending upon a particular date . suppose in the flat file I have last 100 years historic data . But I want only last 50 year's data , into the table .For that I wrote the control file , but it's not working .

    LOAD DATA
    INFILE 't1.out'
    INSERT
    INTO TABLE T1
    --FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
    TRAILING NULLCOLS
    --WHEN TO_DATE(DOB,'YYYYMMDD') > TO_DATE('19540101','YYYYMMDD')

    (
    NAME position(1:4) CHAR
    ,DOB position(5:12) DATE "YYYYMMDD"
    )




    Please help .................
    Last edited by himridul; 05-26-04 at 07:50.
    himridul

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Would this work?

    WHEN DOB > '19540101'

    ...since fortunately your date field is in a format that sorts correctly.

  3. #3
    Join Date
    Jan 2004
    Posts
    66
    Actually it is not working , giving the following error :

    SQL*Loader-350: Syntax error at line 5.
    Illegal combination of non-alphanumeric characters
    WHEN DOB > '19540101'
    himridul

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    How about (and sorry I am just guessing here!):

    WHEN (5:8) >= '1954'

  5. #5
    Join Date
    Jan 2004
    Posts
    66
    No Andrews , it's not working . Actually I think SQLLOADER doesn't support > and < checking . It only does <> and = checking .

    SQL*Loader: Release 8.1.7.0.0 - Production on Wed May 26 17:40:52 2004

    (c) Copyright 2000 Oracle Corporation. All rights reserved.

    SQL*Loader-350: Syntax error at line 5.
    Illegal combination of non-alphanumeric characters
    WHEN (5:8) >= '1952'
    himridul

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Yes, you are right (I just checked the manual). Also, you can combine conditions with AND but not with OR! So how about:

    WHEN (5:8) <> '1953'
    AND (5:8) <> '1952'
    ...
    AND (5:8) <> '1901'

    Grim, I agree.

    If you are on 9i then you could use external tables rather than SQL Loader. Then you can get the data like this:

    insert into t1 (name, dob)
    select name, dob from external_table
    where dob >= to_date('19540101','YYYYMMDD');

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •