wm_concat

building semantic layer
1

Has anyone used wm_concat oracle function in Universe?

Deskiuser (BOB member since 2009-06-05)

2

According to one post on an Oracle forum that I found, the wm_concat() function goes with a product called Work Manager (thus the wm_ prefix). If you are confident that it will always be available, you could use it.

I would tend to stay away from non-standard functions, as their functionality could change with a new release and you wouldn’t get notified.

Dave Rathbun :us: (BOB member since 2002-06-06)

3

is there any other function similar to wm_concat that can be used in BO??

Muna :india: (BOB member since 2008-08-05)

4

try below link.

http://www.morganslibrary.com/reference/wm_functions.html

Rakesh_K :india: (BOB member since 2007-12-11)

5

Hi,

We tried Wm_Concat in a object in the universe in order to get a Concatenated list in an object in our report. We tried but…

Business Objects has a problem defining the correct SQL which uses Oracle. So it does not work unless… we alter the SQL manually. This is not a nice solution! We use BO 3.1 SP1 and oracle 11g R1!
Could it be that this issue (not creating the right SQL) us solved in newer releases of Business Objects (Sp2, sp3> 4!)

TurningPointHolland :netherlands: (BOB member since 2006-09-06)

6

If you are looking for concatenated list in your object, you might even try

LISTAGG

from Oracle. It works good!!

Thanks,
Sowmya.

sowmyabatchu (BOB member since 2009-10-14)

7

I remember using wm_concat() few months back and didn’t find it purely dependable. For some reason, I was not able to keep the ‘Order’ consistent. The function was randomly concatenating values for a set of (similar) ID’s (Consider two columns - ID and Value). Its also an (Oracle) undocumented function.

If you are looking for String Aggregation, I will suggest looking here:

Link: http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

I had a pretty low Oracle version and used SYS_CONNECT_BY_PATH method to perform String Aggregation using Derived Tables.

You can use LISTAGG function as Sowmya suggested. It works on Oracle 11g (May be Release 2) .

Rajat Sapru :us: (BOB member since 2008-08-28)

8

Thanks for the replies… :?

TurningPointHolland :netherlands: (BOB member since 2006-09-06)

9

As far as I remember, BO always created the correct SQL, its the way WM_CONCAT() function works which I didn’t prefer.

Can you share what exactly happens? What part of SQL you have to alter and why?

Rajat Sapru :us: (BOB member since 2008-08-28)

10

If you use the wm_concat in an object definiton the sql is e.g.

The original SQl that is created by Business Objects is :

select sum(measure1), dim1, dim2, wm_concat(dim3)
from table x
group by dim1, dim2, wm_concat(dim3)

if you run this in the query panel you 'll get an error> See attachment

If you alter the SQL and remove the wm_concat object from the group by it works!

Then it would be this:

select sum(measure1), dim1, dim2, wm_concat(dim3)
from table x
group by dim1, dim2

I am using BO XI 3.1 SP1 with Oracle 11g R1
ERROR GROUP BY USING WM_CONCAT.JPG

TurningPointHolland :netherlands: (BOB member since 2006-09-06)

11

And that indeed is the correct behevaior :slight_smile:

I don’t know dutch but that error would translate to “group function is not allowed here” (At least on English DB Versions 8) ) and the reason is because you are using WM_CONCAT() in the group by clause. WM_CONCAT(), just like SUM(), MAX() or MIN() is considered like an Aggregate/Analytic function which cannot be used in Group By Clause (but can be used in Having clause). Try replacing WM_CONCAT() by SUM() and you should end up on the same error.

You will have to change your query a bit (possibly by Sub-Query). I actually used WM_CONCAT() using derived table (since I had to join the result set with some other data set/table). The query I used:

SELECT <ID>, wm_concat(<String>) AS Value
FROM   <Table> 
GROUP BY <ID>

As a word of caution, you may like to stay away from using WM_CONCAT(), especially on Production Environment. (It is not recommended and it really does not have any inbuilt sorting algorithm to order the returned results).

Hope this helps !

Rajat Sapru :us: (BOB member since 2008-08-28)

12

Hi,

8 years later almost…I now face the same need for concatenating row values in 1 column so i will give this a try working with the latest BO 4.2 version :wink: :wink: :lol: :lol:

TurningPointHolland :netherlands: (BOB member since 2006-09-06)