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?
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.