4 Replies Latest reply: Feb 3, 2009 11:48 AM by Jim Stenoish RSS

    SQL Loader combine fields into one.

    jefferam
      Hi i am using sql loader to convert a db from sql server to oracle.
      my SQL Server DB has a field for the date and another for the time, i want to convert this into on filed called date. I cannot seem to figure out how to do this.

      my ctl file looks like this.

      load data
      infile '[Cell_Phones].[dbo].[Account_Details].dat' "str '<EORD>'"
      into table MD_CELLPHONE.Account_Details
      fields terminated by '<EOFD>'
      trailing nullcols
      (
      Account_Number ,
      Phone_Number ,
      Call_temp FILLER,
      Call_Date "TO_DATE(:Call_temp ||' ' ||':Call_Date', 'mm/dd/yyyy HH:miam')"
      )

      BEGINDATA
      3817913.0<EOFD>1234567890.0<EOFD>2007-03-31 00:00:00<EOFD>4:25 PM<EOFD>3817913.0<EOFD>1234567890.0<EOFD>2007-03-24 00:00:00<EOFD>8:19 PM<EOFD>3817913.0<EOFD>1234567890.0<EOFD>2007-03-31 00:00:00<EOFD>4:25 PM<EOFD>


      But if i run this i get the following error

      SQL*Loader-291: Invalid bind variable CALL_TEMP in SQL string for column CALL_DATE.


      basically i cant find a way to do this to combine 2 columns into one and discard the one i am not using.
      Any help would be appreciated.
      Jeff
        • 1. Re: SQL Loader combine fields into one.
          OrionNet
          Hello,

          You can load the data as in a stg table and the move from stg table to regular table concatenating both call_temp and call_date column together.
          load data
          infile 'Cell_Phones.dbo.Account_Details.dat' "str '<EORD>'"
          into table MD_CELLPHONE.Account_Details_stg
          fields terminated by '<EOFD>'
          trailing nullcols
          (
          Account_Number ,
          Phone_Number ,
          Call_temp,
          Call_Date 
          )
          
           Convert  to right date format 
           Insert into account_details (col1 , col2, col3, ...)  select  val1 , val2 , to_date ( val3 || ' ' || val4) ...
           from account_details_stg;
           
          Regards

          Edited by: OrionNet on Feb 2, 2009 5:11 PM
          • 2. Re: SQL Loader combine fields into one.
            jefferam
            I suppose that would work but i am just experimenting with the BOUNDFILLER instead of just filler and i think this might be what i was looking for. Once i get it working i will follow up on this post.
            • 3. Re: SQL Loader combine fields into one.
              OrionNet
              Hello,

              If your data is in fixed format then you can use CONCATENATE and example would be something like this. Remember this works well with the fixed format
                LOAD DATA
                INFILE '....'
                REPLACE CONCATENATE 2 
                INTO TABLE ACCOUNT_DETAILS
               (
                 col1 position(1:40) CHAR,
                 ....
                 col3 position(55:70) ..., -- > Thats how can combine 2 physical fields in logical field
              
                )
               
              Regards
              • 4. Re: SQL Loader combine fields into one.
                Jim Stenoish
                change the line in the control file for field Call_temp to be

                Call_temp BOUNDFILLER

                (Change FILLER to BOUNDFILLER)