Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    11

    Unanswered: Re: using translate for date in the control file !!

    Hi,

    I am having data file where in i expect date but get non-numeric character.

    I am generating control file dynamically (programatically).

    While creating control file i know that here the type of data will be date so i put datatype as date. But at one place i got non numeric character instead of date.

    Now i want to put somekind of logic built in my dynamic generation which will check whether if value within that column is character then make it null and then load data. And if it is numeric keep it as it it.
    /*
    i.e.
    if(non-numericChars)
    make value null before loading.
    else
    keep the value as it and then load it.
    */

    I heard that with translate function we can do that. Right ?

    So if i have a line like this in my control file:

    column_Name date "MM/DD/YYYY HH:MIS AM"

    How should i write the above statement to put the above mentioned logic.

    Any help would be highly appreciated.

    Regards,
    Dipesh

  2. #2
    Join Date
    Jul 2003
    Location
    Peoria AZ
    Posts
    15

    Re: using translate for date in the control file !!

    Try this url may lead you down the right path ..
    http://www.jlcomp.demon.co.uk/faq/cust_fun.html

  3. #3
    Join Date
    Oct 2003
    Posts
    11

    Re: using translate for date in the control file !!

    Hi,

    Thanks for replying.
    But i m not able to make out much from it.

    I want to implement condition like

    dont do anthing to column value if it has number
    other transalte to null if column value contains non-numeric characters.

    Well i will try to sort this out.

    Thanks for providing me the link.

    Regards,

  4. #4
    Join Date
    Oct 2003
    Posts
    11
    Hi,

    I got it my problem solved.
    Actually i was thinking that i will have to check the presence of character and then do translation but i am able to do the translation writing generic translation like this.

    "ColName Date "MM/DD/YYYY HH:MMS AM" Translate('ColName','abcdefghijklmnopqrstuvwxywABC DEFGHIJKLMNOPQRSTUVWXYZ','')"

    So whenever there are some characters instead of date they are replaced by null.

    Thanks !!

    Regards
    Dipesh

Posting Permissions

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