Concatenate Strings in Talend

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
Sixteen 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.
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 
Sixteen 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
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