topic Re: Concatenate Strings in Talend in Talend Studio

Concatenate Strings in Talend

Anonymous — Fri, 08 Apr 2016 12:28:27 GMT

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

Re: Concatenate Strings in Talend

Anonymous — Fri, 08 Apr 2016 13:35:13 GMT

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.

Re: Concatenate Strings in Talend

Anonymous — Wed, 13 Apr 2016 08:54:48 GMT

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

Re: Concatenate Strings in Talend

Anonymous — Wed, 13 Apr 2016 09:34:13 GMT

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

Re: Concatenate Strings in Talend

Anonymous — Thu, 14 Apr 2016 22:03:46 GMT

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