Oracle Table Memo Field in CR 8.5 1

[tmozer]

In one of my Oracle tables there is a memo field that is filled in with key phrases when a certain event is triggered. There is also an event code field that is populated when the event occurs, but the codes are too general. I need to be able to pick specific information for the memo field.

I have already discovered that you cannot use a memo field in the Select Expert in Crystal Reports 8.5. I have not tried anything else yet (I am about to play as soon as I send this), but, assuming I don't stumble on an answer:

Is there anyway in CR 8.5 to parse a memo field for certain words or phrases and use the result in a formula or as a filter?

 

[tmozer]

Lab Case #:C04-03174 Assignment Sequence: 2
Description: Assignment Saved
***Changed Information Follows***
"Lab Code", [CSC] => [CSN]

The above is the contents of the memo field in the table{AUDITLOG.Additional Information} that contains key phrases that I would like to look for (like "Lab Code"). I just banged away at CR 8.5 and could not find a way. I searched the threads briefly and it looks like there might be a SQL conversion to string that might work. But I don't know how to get started......

 

[rosemaryl]

Dear Tmozer,

You can create a SQL Expression to convert the field to something smaller to work with.

Try this: Insert/Field Object/SQL Expression, you must give it a name.

(Substr(Table."Field Name",1,254))

Now you can reference the contents of the %SQLExpName in a formula and not run into the error. If you need to access more then the first 254 characters, create a second substring expression referencing the next 254 characters.

(Substr(Table."Field Name",255,509))

Hope that helps,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[tmozer]

(Substr(Table."Field Name",1,254))

Tryed the above as:

(Substr("AUDITLOG"."Additional Information",1,254)) and get the following error:

ORA-00932 inconsistant datatypes

 

[rosemaryl]

Dear Tmozer,

The unfortunately this indicates that it is an Oracle Long Text field. Long text fields cannot be used in sql expressions .....

Your best bet is to get with your Oracle DBA, explain the issue and ask him/her for help.

Sorry,

ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.