BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

 
Realtime CMS Reporting from Infolytik (Opens a new window)  

General Notice: No events within the next 45 days.

wm_concat
1 members found this topic helpful

 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
Deskiuser
Forum Member
Forum Member



Joined: 04 Jun 2009

Posts: 13



PostPosted: Sun Aug 02, 2009 12:42 am 
Post subject: wm_concat

Has anyone used wm_concat oracle function in Universe?
Back to top
Dave Rathbun
Forum Advocate
Forum Advocate



Joined: 06 Jun 2002
ASUG Icon
speaker.gif*16
Posts: 21976
Location: Dallas, Texas


flag
PostPosted: Sun Aug 02, 2009 8:53 am 
Post subject: Re: wm_concat

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's Adventures in Business Intelligence Image link

Latest Blog Posts
• 2014-08-22 Yes, Virginia, You Can Refresh One Data Provider At A Time
• 2014-07-08 Airlines Could Save Millions in Fuel Costs By Providing Everyone An iPad
• 2014-06-24 Did Florida State Win National Championship By Using Big Data?
Back to top
Muna
Principal Member
Principal Member



Joined: 05 Aug 2008

Posts: 236
Location: Chennai


flag
PostPosted: Mon Oct 05, 2009 6:29 am 
Post subject: Re: wm_concat

is there any other function similar to wm_concat that can be used in BO??
_________________
- Naaz
SAP Certified Associate - BO Web intelligence 3.x
BOCP -CRXI R2
BOCP - BOE Level one | BO XI R2 Migration | BOXI R2 Dashboards |End-to-End DW BI Implementation
Back to top
Rakesh_K
Forum Enthusiast
Forum Enthusiast



Joined: 11 Dec 2007
ASUG Icon
Posts: 1116
Location: India


flag
PostPosted: Mon Oct 05, 2009 7:29 am 
Post subject: Re: wm_concat

try below link.

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

_________________
Regards,
Rakesh K

Impossible is just a big word thrown around by small men who find it easier to live in the world. It's not a fact. It's an opinion.
IMPOSSIBLE IS NOTHING.
Back to top
TurningPointHolland
Principal Member
Principal Member



Joined: 06 Sep 2006

Posts: 271
Location: Gorinchem, Holland


flag
PostPosted: Tue Mar 22, 2011 10:09 am 
Post subject: Re: wm_concat

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!)

_________________
Ton Vermeij
Senior Business Objects and Birst Consultant
BOXI3.1/4.1 /BODI/LO/Dashboards
Gorinchem, Holland, Europe
Owner of www.theturningpoint.nl and co-founder of
www.bi-united.nl (dutch BI-freelancers network)
Back to top
sowmyabatchu
Senior Member
Senior Member



Joined: 14 Oct 2009

Posts: 68



PostPosted: Tue Mar 22, 2011 11:50 am 
Post subject: Re: wm_concat

If you are looking for concatenated list in your object, you might even try
Code:
LISTAGG
from Oracle. It works good!!

Thanks,
Sowmya.
Back to top
Rajat Sapru
Principal Member
Principal Member



Joined: 28 Aug 2008

Posts: 241
Location: Connecticut, US


flag
PostPosted: Tue Mar 22, 2011 1:13 pm 
Post subject: Re: wm_concat

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) .
Back to top
TurningPointHolland
Principal Member
Principal Member



Joined: 06 Sep 2006

Posts: 271
Location: Gorinchem, Holland


flag
PostPosted: Wed Mar 23, 2011 5:29 am 
Post subject: Re: wm_concat

Thanks for the replies... icon_confused.gif
_________________
Ton Vermeij
Senior Business Objects and Birst Consultant
BOXI3.1/4.1 /BODI/LO/Dashboards
Gorinchem, Holland, Europe
Owner of www.theturningpoint.nl and co-founder of
www.bi-united.nl (dutch BI-freelancers network)
Back to top
Rajat Sapru
Principal Member
Principal Member



Joined: 28 Aug 2008

Posts: 241
Location: Connecticut, US


flag
PostPosted: Wed Mar 23, 2011 1:07 pm 
Post subject: Re: wm_concat

TurningPointHolland wrote:
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!)


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?
Back to top
TurningPointHolland
Principal Member
Principal Member



Joined: 06 Sep 2006

Posts: 271
Location: Gorinchem, Holland


flag
PostPosted: Thu Mar 24, 2011 2:53 am 
Post subject: Re: wm_concat

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

_________________
Ton Vermeij
Senior Business Objects and Birst Consultant
BOXI3.1/4.1 /BODI/LO/Dashboards
Gorinchem, Holland, Europe
Owner of www.theturningpoint.nl and co-founder of
www.bi-united.nl (dutch BI-freelancers network)
Back to top
Rajat Sapru
Principal Member
Principal Member



Joined: 28 Aug 2008

Posts: 241
Location: Connecticut, US


flag
PostPosted: Thu Mar 24, 2011 4:29 am 
Post subject: Re: wm_concat

TurningPointHolland wrote:

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


And that indeed is the correct behevaior icon_smile.gif

I don't know dutch but that error would translate to "group function is not allowed here" (At least on English DB Versions icon_cool.gif ) 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:

Code:
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 !
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0475 seconds using 17 queries. (SQL 0.0031 Parse 0.0345 Other 0.0099)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo