Concatenate all MU values into a single column?

, , , , ,
1

My DB model looks like this (In adabas it is a single file with an MU field in it):

In CONNX, it looks like this:
table1 - master table

  • col1
  • col2
    table1-MU1 - this is the child table to table1
  • col10

The relationship between table1 and table1-MU1 is 1:many

I like to have a select statement that returns to me a single row of table1 and in each row, I like to have the values of the MU column return to me in comma-delimited value.

For example, table1 has a row with col1 = “Smith” and isn = 1

In table1-MU1, isn_table1 of 1, I have these rows:

isn_table1 col10
1 first
1 second
1 third

I would like the select to return this row back to me like this: Smith first,second,third instead of

Smith first
Smith second
Smith third

I do not know how to craete a temp varaiable in a select to concatenate or coalesce the values.

The number of rows for each isn in table1 varies so I cannot hardcode col10(1), col10(2), etc.

Please advise.

Thanks,
Min

2

You don’t need a temporary variable, check into the CONCAT function,
in the SQL Gateway User Guide → Chapter 11 (SQL Grammar) → SQL String Functions

3

The concat function would concatenate 3 columns of the same row to form a new value but not to concatenate the value of a column in 3 different rows into a string value. The same goes with a coalesce.

Min

4

ACE generates two kinds of tables for MUs, the “flattened” form, and a “rotated” table,
for this concatenation requirement I’d use the flattened form.

5

I have attached an example, which -

a) is based on the EMPLOYEES-NAT FDT (File 11)

b) Uses the CREATE TABLE DESCRIPTION statement to define a TABLE, that rotares the MU-field ‘AZ’
instead of creating a sub-table.

c) Accesses fields which are NOT NULL, concatenating the MU-Field Values 1 thru 3

concatenated_MU.zip (581 Bytes)

6

I understand the possibility of using the FLAT table. Unfortunately this approach requires us to hard code the number of possible values for this MU field. In Natural, we use 191 occurrences where possible to avoid having to adjust the program when occurrence count possibilities change. This approach is ugly but I may not have a choice in this case.

Thanks for your suggestions.

Min

7

Do you, by any chance, have Adabas Triggers & Stored Procedures available ?

You could then create a trigger that returns the concatenated data without you
having to mess with it on the “client side”.

8

Using Trigger is a good idea. Let me see how far I can go.

Thanks for all of your help.

Min

9

ANSWER:
It is not possible to accomplish what you have described via SQL.
The application will have to build the list of values from the MU and add the commas in between.

QUESTION:
Is it possible to create a query, which returns only one row
where the LANG-values are concatenated (comma-delimited)?

227 ECKHARDT GER,ENG,FRE,SPA

For example -
The following query returns 4 rows:

SELECT e.ADABAS_ISN, e.NAME, l.LANG 
FROM EMPLOYEES e INNER JOIN EMPLOYEES_LANG l 
ON e.ADABAS_ISN = l.ADABAS_ISN 
WHERE NAME = 'ECKHARDT '   
 
227 ECKHARDT GER 
227 ECKHARDT ENG 
227 ECKHARDT FRE 
227 ECKHARDT SPA