question about concat summaryFunction

[claudiobosticco]

Hello, I just wanted to ask if you plan to support a "distinct" version of the concat summary function. It's natively supported by LISTAGG in Oracle 19c (which I'm currently using), I don't know how hard would it be for other databases.

[Blama]

Hi claudiobosticco,

you might be able to trick something with customSelectExpression="distinct myColumn", perhaps even with Velocity to recognize the aggregation-operation binding if the distinct creates problems otherwise.

Best regards
Blama

[claudiobosticco]

Hi Blama, nice idea the customSelectExpression :) but it doesn't work because in Oracle it produces a sql like:

LISTAGG ( '' ||  DISTINCT columnName || '', ', ') WITHIN GROUP (ORDER BY DISTINCT columnName)

where the:

'' ||

in the LISTAGG is the joinPrefix, and then the DISTINCT is also repeated in the:

WITHIN GROUP

Velocity could do the trick, but I was thinking of a general, cross-db solution.

[Isomorphic]

If you are just looking for the equivalent of SELECT DISTINCT, you can do this as a groupBy with no summaryFunctions, as mentioned in the Server Summaries overview.

The word DISTINCT will not appear in the SQL, but the result is the same.

[claudiobosticco]

Hello Isomorphic actually I'm looking for the equivalent of:

LISTAGG (DISTINCT columnName, ', ') WITHIN GROUP (ORDER BY columnName)

as a variant of:

LISTAGG (columnName, ', ') WITHIN GROUP (ORDER BY columnName)

without the DISTINCT in the LISTAGG. Now I'm generating it with a concat summaryFunction and a groupBy, but I've got duplicated values in the resulting concatenation.

[Isomorphic]

We don't (yet) have a declarative way to generate SQL like that (it could be sponsored if you want).

Just to clarify, it looks like what you want is close to our "concat" summaryFunction, except you also want duplicate removal, and ordering, right?

[claudiobosticco]

Hello, actually I already see ordering in the WITHIN GROUP, it seems it's generated by default.

Using the suggestion Blama gave me in post #2, it doesn't work because it puts the DISTINCT also after the ORDER BY, and also because even if the joinPrefix is null, there's always the empty string prefix concatenated in the LISTAGG.

[Isomorphic]

So just to clarify, your using the "concat" summaryFunction with Oracle, and the only issue with the SQL you're seeing generated is that you want DISTINCT added - you've already got the "WITHIN GROUP ( ORDER BY" piece that you want?

If so, we would probably represent this as an additional summaryFunction, "concatDistinct".

[claudiobosticco]

So just to clarify, your using the "concat" summaryFunction with Oracle, and the only issue with the SQL you're seeing generated is that you want DISTINCT added - you've already got the "WITHIN GROUP ( ORDER BY" piece that you want?

yes, exactly

If so, we would probably represent this as an additional summaryFunction, "concatDistinct".

sounds fair

[Isomorphic]

OK - your organization is in the midst of purchasing an unrelated Feature Sponsorship, so you might ask to have this one thrown in.

Note it's not as trivial as it may seem, since for DBs / DataSources that don't support "concat" directly, we implement a Java fallback, and we also support all the aggregation functions client-side in clientOnly DataSources.