MEMO fields in CR8.5

[jwa6]

RE: thread766-1143318

I had the same issues as mentioned in thread thread766-1143318 .

I could not put a formula on a sql field from cr query builder as the report showed the query fields as 'memo'.

I ended up adding an outer select and doing a substring to bring the fields to 10 and 12 char lengths. When I opend the report on the new sql / substrings they magically became 'string'. Now I can edit and adapt to the reports requirements.

the sql was in oracle 9
-- new
select
substr (I3.ori,1,12) ori,
substr (I3.sid,1,10) sid,
substr(I3.arrestdate,1,8) arrestdate
-- new

from
(select
substr (I2.data,I2.osplus6, I2.oriend- I2.osplus6 ) ori,
substr (I2.data,I2.sidplus6, I2.sidend- I2.sidplus6 ) sid,
substr (I2.data,I2.ardtplus6, I2.ardtend- I2.ardtplus6) arrestdate

 

[synapsevampire]

Is there a question in this post?

btw, your sql statement is incorrect as it doesn't select from a table, and you could have used SQL Expressions in Crystal to do the same thing.

How did you use this SQL, are you speaking of a View, or?

The fields are recognizeed as strings as long as they are under 255 characters, if over then Crystal 8.5 and below considers them memo fields and won't allow them to be used in formulas, except SQL Expression formulas.

-k

 

[jwa6]

Is there a question in this post?

no, but there is alot of questions in the thread I referenced
on memos field in CR 8.

RE: thread766-1143318


btw, your sql statement is incorrect as it doesn't select from a table, and you could have used SQL Expressions in Crystal to do the same thing.

yes, as you see it it is incorrect. I have 6 imbedded sqls on top of what you see. Im selecting out data tags from a oracle clob. The embedded sql I found necessary to substring the end and beg of each data tag of varible length. I suspected it may just add more confusion to a confusing topic. Could I have been wrong? -- possibly.

I dont see ( cr 8.5) on what an sql expression is or how to reference. Ill do a search in help files tomorrow, but thats a new one on me and thanks for the tip. I 'play' in this once in a while..enough to get in trouble


I ran the sql in oracle before dropping in CR8. The length didnt seem anywhere 255 in the sql/plus window. not sure...but the substring to a finite field length did convert the 'memo' fields in CR8 to string - I was able to take the date(now string not memo) out of the oracle sql and put it into a readable format for display in cr8

thanks for the input
jwa

 

[synapsevampire]

If the field is defined as 255 or more, then CR 8.X and below sees it as a memo, field lengths are determined by the database, not the clients accessing them as that would be prove very inefficient.

If you look in your list of available formula types, you'll see Formula, Running Total and SQL Expressions, providing you are using a SQL compliant database or connectivity, note the OR as you can use SQL against data sources such as text files.

Since you're using Oracle, you'll be fine.

-k

 

[jwa6]

If the field is defined as 255 or more, then CR 8.X and below sees it as a memo, field lengths are determined by the database, not the clients accessing them as that would be prove very inefficient.

-- I agree and concur


If you look in your list of available formula types, you'll see Formula, Running Total and SQL Expressions, providing you are using a SQL compliant database or connectivity, note the OR as you can use SQL against data sources such as text files.

-- thanks for this tip. Ill assume there is alot of power I was unaware of here. We are moving many user defined CR into enterprise..for the access it provides and the abilty to schedule and email. We are doing the schedule and mail in oracle procs now.
-- cheers and have a guiness on me!

 

[synapsevampire]

Good luck with the CE migration, I think that you'll be very happy with it.

SQL Expressions basically insert what you put in them on the SELECT line of your SQL, so you can even do corrulated subqueries within, eliminating the need for subreports. Things like SUBSTR are common, as is a CAST and even decode statements, helping to offload processing to the database.

Guiness it tis...

-k

 

[PCSAARON]

See my thread: thread766-1258688

I dont agree with CR 8.X seeing a string over 255 as a memo ALL the time! I am having issues myself with memo/string fields. There is a BUG somewhere!

Somehow "something" looks at the data that is in the table to determine the string length of the field in CR.

If anyone has any suggestions, please let me know! Thanks.

Aaron

 

[synapsevampire]

Sure, I suggest that rather than guessing at this based on a toy database, read up on it, Crystal has stated this for years, and you'll find 100's of posts here containing this.

So disagree with everyone if you like, including the software manufacturer, but it's your database/connectivity at fault, not Crystal.

My last contract started to implemnt a MySQL database and 1/2 through elected to drop MySQL because of the ODBC connectivity being flighty (not related to Crystal).

I had from the start stated that I would never consider using it for a Mission Critical application because of the lack of support and historically buggy nature. Were employees dependent upon me to provide their livelihood, I couldn't in good faith go that route any more than I'd consider using Macs in a business environment.

If I want bragging rights about being terminally hip or being a scenester (which using a Mac proves the reverse to me for non graphic oriented people), or just to bash MS, IBM, Oracle, etc., or I'm flat broke then it makes sense.

Right, MySQL has gotten better, they even have Views now...

<yawn>

-k