SQL expression field to avoid memo in formula

[GaryFrost]

I have a VARCHAR2(366) field called TERM_TEXT in an Oracle Database that when it is displayed in Crystal Reports 8 is shown as a memo field.

I want to write a formula find the number of occurances of the character "X" in this field.

I've looked at previous answers to this problem and as views are not an option I have looked at SQL Expression fields.

However, when I create the following SQL Expression Field
SUBSTR(NEW_BASE_TIME.TERM_TEXT, 200)

to try and bring back the first 200 characters of the field
(I intend to bring back the first 200 chars and then the last 166 chars and do a formula on each half and then sum the results)

when I save it I get the error
ORA-01007 variable not in select list
Error in compiling SQL Expression : Invalid field found here

The field is displayed in the details of the report and does appear in the Database-Show SQL Query window as a normal field.

I've never used SQL Expression fields before. Am I missing something obvious here?

 

[lbass]

You could try the following:

{fn SUBSTRING(NEW_BASE_TIME.`TERM_TEXT`, 1, 200)}

where "1" is the starting position of the substring.

-LB