SELECT LIST(attribute, ',')

Questions
1

I assume the concatenating of all rows in a single list of values is not supported in Query Builder. Is that correct?

In SQL Anywhere, the code looks something like the following:

 -- Concatenate values from all city row names into single, comma delimited value 
 SELECT LIST(name, ', ') FROM cities;

I think, in SQL Server it’s XML PATH instead of LIST, but I am not quite sure.

Best regards,

2

With queryBuilder you should be able to basically call any (custom) function of a database by using

q.result.add(q.functions.custom(YOURFUNCTION, [YOURFUNCTION_ARGS])

In SQL Server I usually use the function STRING_AGG for these things. A sample query could look like this (example is rather stupid):

var q = datasources.db.northwind.orders.createSelect();

/** @type {QBJoin<db:/northwind/order_details>} */
var jOrdDetails = q.joins.add(datasources.db.northwind.order_details.getDataSource(), QBJoin.LEFT_OUTER_JOIN);
jOrdDetails.on.add(q.columns.orderid.eq(jOrdDetails.columns.orderid));

q.result.add(q.columns.orderid);
q.result.add(q.functions.custom('STRING_AGG', [jOrdDetails.columns.productid, '; ']), 'product_ids');

q.groupBy.add(q.columns.orderid);

var ds = databaseManager.getDataSetByQuery(q, -1);
3

Hi Patrick

Thanks for the code samples, it’s very useful :-)

For one of the attributes, we have following SQL code. In QB, we don’t find a way to implement the DISTINCT for the attribute “tc.untis_lesson_class”.

LIST (DISTINCT(tc.untis_lesson_class), ', ')

Any idea?

4

Sorry, the code line does look like this and of course not as the one I posted:

LIST (DISTINCT(tc.untis_lesson_class), ', ' ORDER BY tc.untis_lesson_class)

Any idea?

5

Hi Robert,

The custom function can be nested as well, try something like this:

q.result.add(q.functions.custom('LIST', [q.functions.custom('DISTINCT', [q.columns.untis_lesson_class]), '; ']))

Rob

6

Thanks Rob. The SQL code part

LIST (DISTINCT(tr.untis_assigned_room), ', ')

translated to QB as

.add(query.functions.custom('LIST', query.functions.custom('DISTINCT', tr.untis_assigned_room), ', '))

works perfectly.

What I can’t translate is

LIST (DISTINCT(tr.untis_assigned_room), ', ' ORDER BY tr.untis_assigned_room)

Tried for example

.add(query.functions.custom('LIST', query.functions.custom('ORDER BY', tr.untis_assigned_room), ', ')).distinct = true

but get the error “Ein Ausnahme-Fehler ist aufgetreten: 217 java.lang.IllegalArgumentException: Not a valid sql name: ORDER BY”

Any idea how to add the ORDER BY part to the LIST function in QB?

7

Hi Robert,

I guess you are hitting the limits of what you can do with the custom function.
If you do without the order-by, can you sort afterwards in your solution?
Maybe in a column converter?

Rob

8

Hi Rob

Thanks for the idea, which I will try later on. For the moment, I leave the original SQL as it is - it works fine.