OraFAQ Forum: Server Utilities » How to concatenate 2 fields

Home » RDBMS Server » Server Utilities » How to concatenate 2 fields (URGENT: AIX SERVER)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to concatenate 2 fields [message #472205]

Thu, 19 August 2010 04:50

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

Report message to a moderator

Re: How to concatenate 2 fields [message #472264 is a reply to message #472205]

Thu, 19 August 2010 08:26

Mahesh Rajendran
Messages: 10708
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.

Report message to a moderator

Re: How to concatenate 2 fields [message #472282 is a reply to message #472205]

Thu, 19 August 2010 13:10

joy_division
Messages: 4963
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",
.
.
.

Report message to a moderator

Re: How to concatenate 2 fields [message #472288 is a reply to message #472282]

Thu, 19 August 2010 13:50

Barbara Boehmer
Messages: 9102
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")

Report message to a moderator

Re: How to concatenate 2 fields [message #472370 is a reply to message #472288]

Fri, 20 August 2010 04:07

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

Report message to a moderator

Re: How to concatenate 2 fields [message #472379 is a reply to message #472370]

Fri, 20 August 2010 04:40

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

Why can't you use bind variables?

Report message to a moderator

Re: How to concatenate 2 fields [message #472423 is a reply to message #472370]

Fri, 20 August 2010 08:25

Barbara Boehmer
Messages: 9102
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.

Report message to a moderator

Previous Topic:	Loading data via Sql Loader into Varray Tables
Next Topic:	handling errors in batch file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 11 01:46:27 CST 2025