Need Help Splitting String

[khdavis]

Hi, I'm using CR10 with an Oracle database.

I need to split a field where the elements are separated by a carriage return (Chr(13)).
I'd like to be able to split this field into it's individual elements and then group by each element. The field can have any number of elements by probably not more than 20.

I initally have tried to use the formula below (for the first element), but it's not working.

split({APPROVALM1.CURRENT_PENDING_GROUPS},Chr(13))[1]


Can anyone help me?

Thanks in advance.

 

[synapsevampire]

You won't be able to group data by each element as they are not seperate rows, that's not what grouping is.

So before we proceed to assist you, please post technical information:

Example data
Expected output

As for your formula, it's looks fine for pulling the first element of an array providing it's sperated by a chr(13).

How do you know that it is?

It might be a chr(10) (line feed)?

You need to test it, so try:

instr(chr(13),{table.field})

If it reports > 0, then you have a chr(13), also try chr(10).

I suspect that if you need to group by every sub element within that field, you're going to need to do some advanced databsae work, and you should check with whomever is pretending to be a dba there for their solution.

I suspect that a well executed transform statement will pull it off. If you get to the point where you need to treat these as seperate rows, use the Oracle forums here, there are some really sharp Oracle people over there.

Remember, bad database designs generally are better served to be addressed on the database side, not in a client front end.

-k