How do I sum values in a field that contains array values?

building semantic layer
1

I am building a Universe on an Oracle 9i database. There is a field in the table that contains values like this:

[1][1][][][1]
[5][1][2][1]

I would like to code the field so that the output returns values like this (sum the values in the array):

3
9

Is there a way to do this without doing a massive decode statement?

ahoffard (BOB member since 2008-05-15)

2

It look likes this should all be split out at a database level before you even get Designer involved. Or, to answer your question, no. :wink:

3

Is the number of array values constant? In your example you have five slots in the first row and only four in the second.

I think the best option is to write a custom pl/sql function that you can call in the universe. That way if the data ever changes you can update the database function and leave the universe + reports alone. Otherwise you can probably break it out with some string functions / conversion functions / or as you suggest, a nasty decode. :slight_smile:

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