Extracting values fro a string to use to access another table

[sange40]

We are using Crystal Reports 9 and have an Oracle SQL database.

I have a field with a maximum number of 100 characters that comes off of one table. The string is similar to this

BNE/ADL/SYD/ROCK/MEL

I need to extract each of the values delimited by the / and use this value to lookup another table to get a full description.

ie BNE - Brisbane

Once I have the description, I need to string the names back together

ie Brisbane/Adelaide/Melbourne

Any ideas on how I can do this? This is my second Crystal Reports attempt and there is nothing obvious in the refernce books I have access to.

 

[bdreed35]

The best way I can think of is to use a Stored Procedure. You would be able to pull your values from the string, query the lookup table, and then return the records you need to Crystal. If this is not an option, you may be able to use a subreport.

Potentially, your main report could pull the record(s) that have your 100 character string. Then, create a shared array to pass the broken out values of the string. You could then pass the array to a subreport. In the subreport, the report source could be the lookup table. The record selection criteria could be based upon the passed array. You can then build a variable to concatenate the descriptions back together.

I know this may be a little high level so if you need help as your go, post those questions, and I will try to help.

~Brian