Error with memo field and (substr( table.field,1,254))

[tellamugali]

Database is Intersytems Cache database
Connection is via an ODBC System DSN Data Source using Intesystems ODBC 4.00.836.00 driver as we are still running Cache 3.0 FDBMS database.

I am using CRXI in an attempt to search a memo field when I create an SQL expression (substr( table.field,1,254)) I get the below error message:

Error in compiling SQL Expression :
Database Connector Error: 'HY000:[Caché ODBC][State : S1000][Native Code 400]
[C:\Program Files\Business Objects\CRXI\Crystal Reports 11\crw32.exe]
[SQLCODE: <-400>:<Fatal error occurred>]
[Cache Error: <<UNDEFINED>Compile+23^%qserver>]
[Details: <FLoop - func = DQ>]
[%msg: < SQL ERROR #1: `)' expected, `(' found^SELECT ( substr (>] [Database Vendor Code: 400 ]'.

 

[dgillz]

You say "in an attempt to search a memo field" yet your code would seems to be an attempt to return the first 254 characters, no searching involved.

Which is it? If there is more to your SQL, please post it.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[synapsevampire]

Since I don't know Cache syntax, I can't advise you well on that level.

And since you're using CR XI, and it supports fields greater than 254, I see no reason to use it anyway.

If you do really need to do a search of the field, why not do the search in the SQL Expression instead of returning a truncated field?

I suggest that you post requirements rather than designating architecture, because what you're doing doesn't make sense.

-k

 

[tellamugali]

I am searching the memo field for a Doctors name.

The report consists of 2 fields
{EP_VisitNumber.EPVIS_VisitNumber}
{EP_VisitTestSetData.VISTD_Comments}

I have hardcoded the Visit Number because I know the memo field for this visit has the string I am looking for

{EP_VisitNumber.EPVIS_VisitNumber} = "D1041185"

Displays the complete memo field {EP_VisitTestSetData.VISTD_Comments} and the "Dr Name" string is in it. When I apply the like formula

{EP_VisitNumber.EPVIS_VisitNumber} = "D1041185" and
{EP_VisitTestSetData.VISTD_Comments} like "*Dr Name*"

I get the below error message.

Failed to retrieve data from the database.
Details: HY000:[Caché ODBC][State : S1000][Native Code 70]
[C:\Program Files\Business Objects\CRXI\Crystal Reports 11\crw32.exe]
[SQLCODE: <-70>:<Multi-Line Field only valid for LIKE, Contains ([), or NULL Comparison.>]
[Details: <FLoop - func = DQ>]
[%msg: <; Compilation Errors: SQL ERROR #70: Multi-Line Field only valid for LIKE, Contains ([), or NULL Comparison.
SQL ERROR #52: Cursor C00000 was not DECLAREd
SQL ERROR #52: Cursor C00000 was not DECLAREd
SQL ERROR #52: Cursor C00000 was not ...


Therefore I was trying to break the memo field into 255 characters to see if it would help.

Any suggestions?

I also attempted to export as ODBC to Access 2000 to create a data source I can manage but the max field size is 255 characters.

 

[tellamugali]

Problem was solved with

instr({Table.Field}, "search string") > 0