How do I write the SQL expression for memo field

[AyJayEL]

I need to use a memo field in a formula. Reading through the other queries here I have worked out that I need to create an SQL expression. This is what I have (substr(MEMO_STR_Choices.MEMO_DATA,1,250)) but when I try to save it I get a message saying invalid identifier. I think this is possibly because I couldn't see the MEMO_DATA field and hand typed it in. Can anyone advise me on this?

I am using Crystal 8 on an oracle database.

Learn something new every day
Using Crystal 8, Oracle Database

 

[IanWaterman]

I find it best to write my query in something like Toad and then wrap in brackets () and copy into sql expressions.

That way you know it will work and not get rejected by crystal.

Ian

 

[AyJayEL]

Just tried that but no luck.

Learn something new every day
Using Crystal 8, Oracle Database

 

[lbass]

You need to determine the correct punctuation for your database, and if it's Oracle, it might require double quotes around your table and field names. Check the "Show SQL Query" for your report to see what the correct punctuation is.

If substr doesn't work, try:

{fn substring("MEMO_STR_Choices"."MEMO_DATA",1,250)}

-LB

 

[AyJayEL]

Tried your formula but it didn't like the function then it didn't recognise substring.

I've got this now {substr("MEMO_STR_Choices"."MEMO_DATA", 1,250)} but it now gives me a syntax error message.

Learn something new every day
Using Crystal 8, Oracle Database

 

[lbass]

Either you can use:

(substr("MEMO_STR_Choices"."MEMO_DATA", 1,250))

...if that is the correct punctuation (did you check?) or:

{fn substring("MEMO_STR_Choices"."MEMO_DATA",1,250)}

The curly brackets are only for functions starting with "fn".

-LB

 

[synapsevampire]

You should post what the actual data type is in Oracle.

Anyway try:

substr(string,1,250)

Try the Oracle forums for advanced Oracle functionality.

-k

 

[AyJayEL]

Synapsevampire, I'm afraid I don't know a lot about Oracle, I simply design crystal reports. As far as I know the field I am using is a Memo field and therefore a type of string. I may be wrong though. You'll have to talk to me like a dummy again.

My version of Crystal doesn't recognise substr (it doesn't go blue when I type it).

If I try this (substr("MEMO_STR_Choices"."MEMO_DATA", 1,250)) I get the ) is missing with the cursor after the first ( in the statement.

If I try this {fn substring("MEMO_STR_Choices"."MEMO_DATA",1,250)} I get this field name is not known with the cursor at the start of the statement.

If I try this Left ("MEMO_STR_Choices.MEMO_DATA",250 ) (experimenting) it displays the words MEMO_STR_Choice etc which I sort of expected.

The report I'm trying to 'fix' was designed by someone else and is pretty complex. A person can have up to six Choices and against each choice is a Memo. I have to show the appopriate data from the memo field for each Choice. So Choice 1 should show the memo 1 data against it. I can show all the other data for each choice but I can't choose the memo field in the formula because, as you know, you can't use a memo field in a formula. (Am I in a catch 22 situation?)

Learn something new every day
Using Crystal 8, Oracle Database

 

[lbass]

Are you entering these attempts in the right area? You should be trying these in field explorer->SQL expression->new (NOT in the formula editor).

-LB

 

[AyJayEL]

Doh, I've been off for a few days.

Anyway, if I use this in the SQL expression (substr("MEMO_STR_Choices"."MEMO_DATA", 1,250)) I get a message saying 'inconsistent datatypes, expecting number got long'.

If I use {fn substring "MEMO_STR_Choices"."MEMO_DATA",1,250)} I get invalid function with the cursor before substring.


Learn something new every day
Using Crystal 8, Oracle Database

 

[DataDivaJill]

It was my understanding you can't use a memo field in a formula at all. I've never been able to anyway.
Jill

 

[lbass]

You can type them into SQL expressions (they won't appear on the field list).

I use Oracle as a datasource, and either of my earlier suggestions work with memo fields, so maybe this has to do with your datatype (one of the last error messages seems to suggest this). I don't really have any other suggestions. Sorry.

-LB

 

[AyJayEL]

Well thank you for trying anyway. But if I do find a solution I'll post it here.

Learn something new every day
Using Crystal 8, Oracle Database