How to concatenate different rows into one field?

[Dome400]

Hi All,

I need to save the content of a source file member into a single field of a table.

1) Basically I have a temporary source file member:

CRTSRCPF QTEMP/QRULE

2) Containing one single mbr where the user can write in it some information:

STRSEU SRCFILE(QTEMP/QRULE) SRCMBR(RULE)

3) now I have to UPDATE a table MYLIB/RULES having RULE_FIELD VARCHAR(10878) =

concatenate all SRCFILE(QTEMP/QRULE) SRCDTA field

In this way RULE_FIELD will contain

QRULE(ROW1).SRDTA concat RULE(ROW2).SRDTA concat RULE(ROW3).SRDTA concat....


Hopefully my request is clear enough.

[Dome400]

I've just come out with this SQL which seems to work.. now I only need to update my field with the sql result:

SELECT max(CAST(SYS_CONNECT_BY_PATH(srcdta, '')
AS VARCHAR(10878))) AS ORG
FROM qrule
START WITH srcseq = 1
CONNECT BY NOCYCLE prior srcseq + 1 = srcseq

[Dome400]

Now, it is not as easy as I thought..unfortunately..

second problem I am encountering is, when executing the update sql:

update RULES
set rule_field = (
SELECT max(CAST(SYS_CONNECT_BY_PATH(srcdta, '')
AS VARCHAR(10878))) AS ORG
FROM qrule
START WITH srcseq = 1
CONNECT BY NOCYCLE prior srcseq + 1 = srcseq )
where name = 'TEST1'


I get the sql error message:

Message ID . . . . . . : SQL7008 Severity . . . . . . . : 30
Message type . . . . . : Diagnostic

Message . . . . : RULES in MYLIB not valid for operation.
Cause . . . . . : The reason code is 3. Reason codes are:
3 -- RULES not journaled, no authority to the journal, or the journal state
is *STANDBY. Files with an RI constraint action of CASCADE, SET NULL, or
SET DEFAULT must be journaled to the same journal.


Solution should be journaling the file (according to the message suggestion) but it is something I don't want to do and then I need to found another solution.

[Viking]

Try adding WITH NC at the end. Or do this first: set option commit=*none

You want to tell it to not use commitment control.

[UserName10]

It might be easier to spin through file QRULE and load field SRCDTA into a userspace, advancing the pointer for each record read. Then update file RULES from a varchar field based on a pointer to the beginning of the userspace. Could probably skip SQL altogether. On second thought just skip the userspace and load SRCDTA into a 10878 work field using base/offset addressing, same thing.

[Vectorspace]

Try the listagg() function: https://www.ibm.com/support/knowledg...collistagg.htm

[Dome400]

unfortunately we still are at 7.1 (what a shame, isnt it?) which, if Im not worng, does not support LISTAGG ..

[Vectorspace]

That's unfortunate.

What I would do in your situation is what UserName10 recommended - write a program to read through and concatenate the records.

[Dome400]

Hi Vectorspace..

the sql posted here before works, btw ;-)