[SOLVED] Documentum DQL join by repeating attribute

Documentum DQL join by repeating attribute

I tried to join these tables using repeating attribute:

table1:

r_object_id           codes
...                   1,2,3,4,5
...                   7,6,3,4,5
...                   1,5,4,2,3

Where codes attribute is the repeating attribute.

table2:

r_object_id      name      code
...              hello      1
...              aba        2
...              father     3
...              mother     4
...              hello2     5
...              hello3     6
...              hello4     7

I want result like this:

table1.r_object_id      names
...                     hello,aba,father,mother,hello2

What can I do?

Solution

This is not possible in one DQL query. But you have some options how to solve it.

1) Using one DQL but having one row per one repeating value:

SELECT DISTINCT t1.r_object_id, t2.name FROM table1 t1, table2 t2 WHERE t1.codes = t2.code ENABLE(ROW_BASED)

The result will be like this:

r_object_id      name
0900ad1234567890 hello
0900ad1234567890 aba
0900ad1234567890 father
0900ad1234567890 mother
0900ad1234567890 hello2
0900ad1234567891 father
0900ad1234567891 mother
...

2) Pair values in an application - for example using Java. Where you select all records from the table2 by one query and stores them into Map<String, String> codeTable, where code attribute is as a key and name attribute as a value. Then select records from table1 by another query and pair values from the repeating attribute (codes) with values from the codeTable map.