[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1824
  • Last Modified:

SQL *Loader Question

Hi,

I am very new to SQL *Loader. We are starting to use SQL *Loader to load the data in to a table.
If I want to populate a column in the table concatinating with two other columns together..

Is it possible to do this???

I am posting my code.


LOAD DATA
   INFILE 'XXX.dat'
   REPLACE
   INTO TABLE LOAD
  (A POSITION(05:19) CHAR,
   B    POSITION(189:197)  CHAR,
    C  EXPRESSION "TRUNC(Last_day(add_months(sysdate, -1)))",
    D  EXPRESSION "A||C" 'populate D column woth A AND C)

Any help is appriciated.


Thanks
Madhavi



0
Regulapati
Asked:
Regulapati
  • 2
  • 2
1 Solution
 
MikeOM_DBACommented:
Try this:

LOAD DATA
   INFILE 'XXX.dat'
   REPLACE
   INTO TABLE LOAD
  (A POSITION(05:19) CHAR,
   B    POSITION(189:197)  CHAR,
    C "TRUNC(Last_day(add_months(sysdate, -1)))",
    D  ":A||:C")

0
 
RegulapatiAuthor Commented:
HI Mike,

Thanks for the reply.

Did not work,


Error Message is  "not all variables bound" on
SQL string for column : ":A||:C" line.

Please let me know if I have to do any thing else...

Thanks
Madhavi

0
 
seazodiacCommented:
the problem here is that C column is also derived from a function and it's a DATE data type.

can you try this ?

LOAD DATA
  INFILE 'XXX.dat'
  REPLACE
  INTO TABLE LOAD
 (A POSITION(05:19) CHAR,
   B    POSITION(189:197)  CHAR,
   C "TRUNC(Last_day(add_months(sysdate, -1)))",
   D  ":A||to_char(:C)")
0
 
MikeOM_DBACommented:
OK, or maybe like this:

LOAD DATA
   INFILE 'XXX.dat'
   REPLACE
   INTO TABLE LOAD
  (A POSITION(05:19) CHAR,
   B    POSITION(189:197)  CHAR,
    C "TRUNC(Last_day(add_months(sysdate, -1)))",
    D  ":A||TRUNC(Last_day(add_months(sysdate, -1)))")
0
 
RegulapatiAuthor Commented:
Hi Mike and  seazodiac,

Thanks for the reply.


I tried Mike's Solution and it worked.


Thanks again for the help


Madhavi
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now