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,
COMMENT_NUMBER AS VAL_INDEX
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.
I am not sure why you are getting that error. But I did find this on the forums from 2004. Maybe it will help?
I was also thinking you could do it with an ACROSS and a hold file - something like:
TABLE FILE CAR
ACROSS COUNTRY NOPRINT
I hope that helps!
WF Reporting Server 71x, 76x
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.
|Powered by Social Strata|