cancel
Showing results for 
Search instead for 
Did you mean: 

STRING_AGG order by

0 Kudos

Hi Guys,

I came across a nifty little function STRING_AGG()

I'am using the function to describe a process and its steps.

SELECT BP.BUSINESS_PROCESS_ID as BP,

       STRING_AGG(BP.STEP_TYPE,',') as STEP_SUMMARY

FROM "BUSINESS_PROCESS" BP

GROUP BY BP.BUSINESS_PROCESS_ID

From this I should receive each BUSINESS_PROCESS and concatenation of its steps.

Which gives me an overview of the process life-cycle.

Like;:

BP      | STEP_SUMMARY

123       CREATED,CHANGED,APPROVED,DELETED

Unfortunately, when i use this it comes back in an random unsorted manor.

In the HANA SQL guide the function is described here.

Expressions - SAP HANA SQL and System Views Reference - SAP Library

It says I can use an aggregate_order_by_clause

SELECT BP.BUSINESS_PROCESS_ID as BP,

       STRING_AGG(BP.STEP_TYPE,',' ORDER BY STEP_ID) as STEP_SUMMARY

  FROM "BUSINESS_PROCESS" BP

  GROUP BY BP.BUSINESS_PROCESS_ID

But when I add the statement I get an error.

SAP DBTech JDBC: [257]: sql syntax error: incorrect syntax near "ORDER": line 1 col 55 (at pos 55)

Has anybody had any success with this order_by_clause

or any suggestion on how to overcome this problem

or has the syntax changes

or is this a bug

'We are on rev.97.

Best Regards,

Rasmus

Accepted Solutions (1)

Accepted Solutions (1)

lucas_oliveira
Advisor
Advisor
0 Kudos

Hi Rasmus,

This was already discussed here. Order by in function string_agg is only available from SP10 on.

Please take a look into the other discussions with the same kind of question:

BRs,

Lucas de Oliveira

0 Kudos

Thanks, that explains it.

Answers (0)