using a DB2 as database this SQL (passthrough)
with "SQL2" as (
select ID, string1
from ( SELECT
ID,
string1
FROM (
select "ID" as ID, "string2" as string1 FROM (
select '0815' as ID, '1234' as string2
from SYSIBM.SYSDUMMY1
union all
select '0815' as ID, '5678' as string2
from SYSIBM.SYSDUMMY1
) ) as t1
) t2
)
select "SQL2"."ID" "ID" ,
xmlserialize( xmlagg(xmltext(CONCAT( ', ',STRING1))) as CHAR LARGE OBJECT (200) ) AS "keyword concatenated"
from "SQL2" GROUP BY "SQL2"."ID" FOR FETCH ONLY
results in
| 0815 | , 1234, 5678 |
The SQL can be used in Frameworkmanager or Report-SQL.
In my tests I had to use the option "pass-through" instead of "native".
The part:
select 1 as ID, 2 as string1 FROM (
select '0815' as ID, '1234' as string2
from SYSIBM.SYSDUMMY1
union all
select '0815' as ID, '5678' as string2
from SYSIBM.SYSDUMMY1
Should be replaced by some query against your real database, e.g. select t1.ID as ID, t2.car_color as string2 FROM mydb.table1 as t1 join mydb.table2 as t2 on table1.ID = table2.ID
The trick is done by GROUPING the ID column and using xml-functions to concat the rows.
On Oracle there are other functions, that do the trick. See:
https://stackoverflow.com/questions/1076011/Speaking in general, the steps to be done are similar. Have a SQL that is grouped and use database functions to transform the results from the rows to just row haveing the former results concated.
Hope this helps.
------------------------------
Ralf Roeber
https://linkedin.com/in/ralf-roeber-470425a/------------------------------
Original Message:
Sent: Fri March 19, 2021 11:07 AM
From: Peter Hess
Subject: Modelling in Framework Manager
Hi Henk,
unfortunately I cannot do anything on the database side. The problem has to be solved in Framework Manager itself. Your ideas are not running for me.
Original Message:
Sent: 3/16/2021 10:02:00 PM
From: HENK CAZEMIER
Subject: RE: Modelling in Framework Manager
Hi Peter,
This question came up a while ago in another conversation:
https://community.ibm.com/community/user/businessanalytics/communities/community-home/digestviewer/viewthread?MessageKey=4a212db4-2b70-426e-a521-ed44040e252a&CommunityKey=6b10df83-0b3c-4f92-8b1f-1fd80d0e7e58&tab=digestviewer#bm4a212db4-2b70-426e-a521-ed44040e252a
// Henk
------------------------------
HENK CAZEMIER
Original Message:
Sent: Tue March 16, 2021 10:04 AM
From: Peter Hess
Subject: Modelling in Framework Manager
Hi all,
I am looking for a solution to the following problem.
From my database I get a table in which I get 1 - N entries for an ID. If I evaluate this table in reporting, e.g. with a repeating element table, the performance is poor.
I would therefore like to concatenate elements into an ID.
original
ID text
01 ABC
01 4711
01 D47 FFF
02 ZGT
02 DD4 DSD
03 THG
and so on.
Target
ID text
01 ABC, 4711, D47 FFF
02 ZGT, DD4 DSD
03 THG
04 ....
Does anyone have any idea how I can best do this in Framework Manager?
Thanks a lot
Peter
------------------------------
Peter Hess
------------------------------
#CognosAnalyticswithWatson