2 Replies Latest reply: Oct 12, 2012 3:24 AM by BluShadow RSS

    Help on SQL Loader When Clause

    user10566312
      I have 2 Oracle tables of exact same structure.


      ENO NUMBER
      PARENT NUMBER
      CHILD NUMBER
      ENAME VARCHAR2 (50 Byte)
      ADDRESS VARCHAR2 (50 Byte)
      CITY VARCHAR2 (50 Byte)
      SRCFILENAME VARCHAR2 (50 Byte)
      SDATE VARCHAR2 (400 Byte)
      Current_Load VARCHAR2 (1 Byte)




      Have a data file and a loader control file which loads data in these 2 tables.
      I need to do a conditional load using a when clause.
      Condition : when PARENT=CHILD, load in table 2 else load in table 1

      My control file looks like this .
      LOAD DATA 
      INFILE 'TEST_20120815.txt' 
      APPEND INTO TABLE test1 
      fields terminated by "|" 
      trailing nullcols 
      ( 
      eno, 
      parent, 
      child, 
      ename, 
      address, 
      city , 
      sdate "to_date(:sdate,'DD/MM/YYYY')", 
      SrcFileName, 
      col7 filler, 
      "Current_Load" constant 'Y' 
      ) 
      INTO TABLE test2 when (parent=child) 
      fields terminated by "|" 
      trailing nullcols 
      ( 
      eno position(1), 
      parent, 
      child, 
      ename, 
      address, 
      city , 
      sdate "to_date(:sdate,'DD/MM/YYYY')", 
      SrcFileName, 
      col7 filler, 
      "Current_Load" constant 'Y' 
      ) 
      But this doesn't work. It works fine if I give direct value like PARENT='P'.

      Any idea as how to compare the 2 fields while doing conditional load?
        • 1. Re: Help on SQL Loader When Clause
          jeneesh
          Better is to use external table if you can transfer the file to DB server..

          Then you can use all the SQL functionalities to filter your data iwth INSERT ALL statement
          • 2. Re: Help on SQL Loader When Clause
            BluShadow
            You cannot compare 2 fields with the WHEN clause in an SQL*Loader control file.
            only literal values as defined by the syntax:

            http://docs.oracle.com/cd/B19306_01/server.102/b14215/ldr_control_file.htm#i1005657

            As already suggested, you would be better to load your data using an external table where you can apply any conditions you want during your SQL query that is fetching the data.