Five Stars

Concatenate Strings in Talend

Hey folks,
I have an Oracle Query which contenate some Fields 
Oracle Query:
SELECT
DISTINCT
(RELQISFB.MNR),
RELQISFB.QISRNR,
RELQTXT.ATTRIB01 ||': ' ||RELQISFB.ATTRIB01|| chr(9) || chr(9)|| chr(9) ||
chr(9)||RELQTXT.ATTRIB02 || ': ' ||RELQISFB.ATTRIB02 || chr(13)||chr(10)||
RELQTXT.ATTRIB03 || ':'||RELQISFB.ATTRIB03 || chr(9) || chr(9)|| chr(9) ||
chr(9)||RELQTXT.ATTRIB04 || ':'||RELQISFB.ATTRIB04 || chr(13)||chr(10)||
RELQTXT.ATTRIB05 || ':'||RELQISFB.ATTRIB05 || chr(9) || chr(9)|| chr(9) ||
chr(9)||RELQTXT.ATTRIB06 || ':'||RELQISFB.ATTRIB06 || chr(13)||chr(10)||
RELQTXT.ATTRIB07 || ':'||RELQISFB.ATTRIB07 || chr(9) || chr(9)|| chr(9) ||
chr(9)||RELQTXT.ATTRIB08 || ':'||RELQISFB.ATTRIB08 || chr(13)||chr(10)||
RELQTXT.ATTRIB09 || ':'||RELQISFB.ATTRIB09 || chr(9) || chr(9)|| chr(9) ||
chr(9)||RELQTXT.ATTRIB10 || ':'||RELQISFB.ATTRIB10 || chr(13 )||chr(10)||
RELQTXT.ATTRIB11 || ':'||RELQISFB.ATTRIB11 || chr(9) || chr(9)|| chr(9) ||
chr(9)||RELQTXT.ATTRIB12 || ':'||RELQISFB.ATTRIB12 || chr(13)||chr(10)||
RELQTXT.ATTRIB13 || ':'||RELQISFB.ATTRIB13 || chr(9) || chr(9)|| chr(9) ||
chr(9)||RELQTXT.ATTRIB14 || ':'||RELQISFB.ATTRIB14 || chr(13)||chr(10)||
RELQTXT.ATTRIB15 || ':'||RELQISFB.ATTRIB15 || chr(9) || chr(9)|| chr(9) ||
chr(9)||RELQTXT.ATTRIB16 || ':'||RELQISFB.ATTRIB16 || chr(13)||chr(10)||
RELQTXT.ATTRIB17 || ':'||RELQISFB.ATTRIB17 || chr(9) || chr(9)|| chr(9) ||
chr(9)||RELQTXT.ATTRIB18 || ':'||RELQISFB.ATTRIB18 || chr(13)||chr(10)||
RELQTXT.ATTRIB19 || ':'||RELQISFB.ATTRIB19 || chr(9) || chr(9)|| chr(9) ||
chr(9)||RELQTXT.ATTRIB20 || ':'||RELQISFB.ATTRIB20 || chr(13)||chr(10)
AS Textverkettung,
relfba.ANR,
RELFBA.RNR
FROM INFOR.RELQISFB RELQISFB
INNER JOIN INFOR.RELQTXT RELQTXT
ON (RELQISFB.GRP = RELQTXT.GRP)
AND (RELQISFB.SPRACHKNZ = RELQTXT.SPRACHKNZ)
INNER JOIN infor.RELFBA RELFBA
ON RELFBA.RNR = RELQISFB.QISRNR
Where relfba.anr = '1001528'


The Result in an CSV gives the following:
"MNR","QISRNR","TEXTVERKETTUNG","ANR","RNR"
"078-....",2862202,"Leistung W: 200 Eingangsspannung V: 400
Frequenz Hz: 50 Ausgangsspannung V DC: 80
Ausgangsstrom A DC:48% Induktion T:VDE 0570 / EN 61558-2-1
Schaltgruppe PRI-sek:Ii3 Schutzart // Schutzklasse:IP  00 // I
Norm:T40/B // DB Zulassungen:5,15
Isolationskl. // Betriebart:Klemmen Anschlüsse Eingang:Klemmen
Anschlüsse Ausgang:1-Kammer (getrennte Wicklungen) Wicklungsart:1
Restwelligkeit %:10 Montage:
Oberflächenbehandlung: Zubehör:

Not perfectly formatted but i think it shows what i want to point out.
How i can achive sth. like that in talend ? 
regards fireskyer
4 REPLIES
Fifteen Stars

Re: Concatenate Strings in Talend

Well, you can use the same SQL and just output to a single column using the tOracleInput. That would be the easiest way since you have already done this. Alternatively you could bring the data in as individual columns and then use a tMap (or tJavaRow, tJavaFlex, etc) to concatenate the data into a single column. The code to do this is simple if you have any Java experience.
Rilhia Solutions
Five Stars

Re: Concatenate Strings in Talend

Hello rhall,
My java exoperience is near non existent
I've sth. read about the T-Aggregate Component. 
Does it work also with the Talend component T-Aggregate ?
regards john 
Fifteen Stars

Re: Concatenate Strings in Talend

Hi John,
You can either just use that exact SQL with the tOracleInput component and output to a single column (built-in schema and create a single varchar column) or use a tMap. The tAggregate component is not what you need. 
With the tMap, you would need to join all of the columns into 1 output column using code similar to below (assuming the input row is "row1"....
row1.col1 +":"+ row1.col2 +":"+ row1.col3 +":"+ Character.toString ((char) 9) +":"+Character.toString ((char) 9)

The "Character.toString ((char) 9)" sections produce the tabs that you wanted in your SQL.
Either one of the methods will work for you
Rilhia Solutions
Five Stars

Re: Concatenate Strings in Talend

Thanks rhall 
I'll try and see if it's fit  .. or may i make this steps on the Basic when i create the sql query 
Thanks for the tips it will be useful in other situations too 
regards john