Home » RDBMS Server » Server Utilities » How to concatenate 2 fields (URGENT: AIX SERVER)
How to concatenate 2 fields [message #472205] Thu, 19 August 2010 04:50 Go to next message
sunil.madnani
Messages: 36
Registered: March 2007
Location: BHILAI
Member
Hi All,
I am running SQLLDR on unix.

LOAD DATA
INFILE F56100c.dat
APPEND
INTO TABLE tstdta.F56100C
(
Z59SFU13 POSITION(14;21),
Z59SFU14 FILLER,
Z59SFU15 POSITION(22;24),
Z59SFU16 POSITION(2;10),
Z59SFU17 POSITION(55;59) + POSITION(51;53) + POSITION(47;49) <--these three fields have to be concatenated
)

I have already used
1.Z59SFU17 POSITION(55:59)||POSITION(51:53)||POSITION(47:49)
getting err:Illegal combination of non-alphanumeric characters
2.Z59SFU17 POSITION(55:59,51:53,47:49)
this is also not working.

3. i can not use \":Z59SFU15 || :Z59SFU16\" this option.


can any one help me on this...

Regards:
Sunil Madnani
Re: How to concatenate 2 fields [message #472264 is a reply to message #472205] Thu, 19 August 2010 08:26 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10615
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It could be helpful if you could post the Oracle version, table DDL and a sample datafile.
Re: How to concatenate 2 fields [message #472282 is a reply to message #472205] Thu, 19 August 2010 13:10 Go to previous messageGo to next message
joy_division
Messages: 4490
Registered: February 2005
Location: East Coast USA
Senior Member
sunil.madnani wrote on Thu, 19 August 2010 05:50
Hi All,
I am running SQLLDR on unix.

LOAD DATA
INFILE F56100c.dat
APPEND
INTO TABLE tstdta.F56100C
(
Z59SFU13 POSITION(14;21),
Z59SFU14 FILLER,
Z59SFU15 POSITION(22;24),
Z59SFU16 POSITION(2;10),
Z59SFU17 POSITION(55;59) + POSITION(51;53) + POSITION(47;49) <--these three fields have to be concatenated
)




Does something like this work:
.
.
.
junk1 filler position(47:49),
junk2 filler position(51:53),
junk3 filler position(55:59),
z59sfu17 ":junk3 || :junk2 || :junk1",
.
.
.


Re: How to concatenate 2 fields [message #472288 is a reply to message #472282] Thu, 19 August 2010 13:50 Go to previous messageGo to next message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
Use boundfiller. Some older versions also require that any calculated fields be at the end of the list.

.
.
.
junk1 boundfiller position(47:49),
junk2 boundfiller position(51:53),
junk3 boundfiller position(55:59),
z59sfu17 ":junk3 || :junk2 || :junk1")
Re: How to concatenate 2 fields [message #472370 is a reply to message #472288] Fri, 20 August 2010 04:07 Go to previous messageGo to next message
sunil.madnani
Messages: 36
Registered: March 2007
Location: BHILAI
Member
Hi thanks alot for replying,
but my problem is i can not use bind variable,
I want to concat using position values only.....e.g.
I have tried the below examples also..
) POSITION(11:10)||POSITION(15:17)||POSITION(01:03) -> Not working
2) POSITION(11:10,15:17,01:03) -> Not working
3)POSITION(11:10)+POSITION(15:17)+POSITION(01:03) -> Not working

Regards:
Sunil Madnani
Re: How to concatenate 2 fields [message #472379 is a reply to message #472370] Fri, 20 August 2010 04:40 Go to previous messageGo to next message
cookiemonster
Messages: 10853
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why can't you use bind variables?
Re: How to concatenate 2 fields [message #472423 is a reply to message #472370] Fri, 20 August 2010 08:25 Go to previous message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
You can't concatenate using positions; It is not valid syntax. Using boundfiller and bind variables is the proper way to do it. If that is not working for you, then you must be doing something wrong and you need to post a copy and paste of what you tried and the results.
Previous Topic: Loading data via Sql Loader into Varray Tables
Next Topic: handling errors in batch file
Goto Forum:
  


Current Time: Wed Jul 30 03:34:30 CDT 2014

Total time taken to generate the page: 0.07923 seconds