Does anyone have a way to concatenate a field from several rows into a field on another table in a single row?

I am trying to take rows of comments in Oracle and put them in one record in another table using an ETL. I have been able to develop and run the code using SQL but when I put it in an ETL, I am getting an error on the CONNECT BY statement (FOC14069) SYNTAX ERROR ON LINE AT 'CONNECT' --Expected ')'. Ths SQL is as follows:


SELECT MC.REPORT_ENTITY AS MACH_RE,
MC.BUSINESS_UNIT AS MACH_BU,
MC.MACHINE_NUMBER AS MACH_NUM,
SUBSTR(TO_CHAR(SYS_CONNECT_BY_PATH(MC.COMMENTS,'_')),2) AS MACH_COMM
FROM (SELECT REPORT_ENTITY,
BUSINESS_UNIT,
MACHINE_NUMBER,
COMMENTS,
COMMENT_NUMBER AS VAL_INDEX
FROM MACHINE_COMMENTS)
MC
WHERE CONNECT_BY_ISLEAF = 1
CONNECT BY (
VAL_INDEX = PRIOR VAL_INDEX + 1 AND
MACHINE_NUMBER = PRIOR MACHINE_NUMBER
START WITH VAL_INDEX = 1)


Does anyone have another way of doing this? Any assistance or suggestions is appreciated.

Hi,

I am not sure why you are getting that error. But I did find this on the forums from 2004. Maybe it will help?

https://forums.informationbuilders.com/eve/forums/a/tpc/...1057331/m/4471008331

I was also thinking you could do it with an ACROSS and a hold file - something like:

TABLE FILE CAR
SUM COUNTRY
ACROSS COUNTRY NOPRINT
END

I hope that helps!

Thanks for the pointing me in the direction of the forums from 2004. I looked before I posted mine and didn't see this.

I was able to get the flow to work using a DBMS SQL Flow and then attaching that to another flow that does the rest of my joins and that appears to be working, but it seems like extra work. I thought there might be a function that does this or a little easier way.