Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Extract first 20 characters from a memo field.
- Thread starter Hillary
- Start date
Try to create an SQL Expression such as:
--------------------
LEFT(your_field, 20)
--------------------
hth,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
--------------------
LEFT(your_field, 20)
--------------------
hth,
- Ido CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.
- Thread starter
- #3
crystalvictim
Programmer
As long as your report is not too complex and you are connecting to your database via ODBC, the following might work:
Start the Crystal SQL Designer and create a query by manually entering the select-statement, e.g.
select table1."field1",table1."field2",substr(table1."field3",1,20) from table1
Save this query and use it as database for your report.
You should now be able to use the 'substringed' field in you formulas.
Start the Crystal SQL Designer and create a query by manually entering the select-statement, e.g.
select table1."field1",table1."field2",substr(table1."field3",1,20) from table1
Save this query and use it as database for your report.
You should now be able to use the 'substringed' field in you formulas.
Hi Hillary,
This is undocumented but it frequently works depending on odbc driver since SQL expression syntax is driver specific. Is your db SQL Server?
Oracle seems to be much more forgiving on expressions - however long text you can do absolutely nothing with. I throw this out there because I don't know what field type your memo field really is.
Open parens are required! (double click another field to get the right format and then manually overtype to get your field name for the memo field.)
SQL Server syntax
(Cast Table."MyMemoField" as varchar(20))
Also little tip, you can do case when's in SQL Expressions and that is not documented either!
(case when table.field = 'Y' then 'abc' else 'def' end)
Just make sure to wrap in parens
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
This is undocumented but it frequently works depending on odbc driver since SQL expression syntax is driver specific. Is your db SQL Server?
Oracle seems to be much more forgiving on expressions - however long text you can do absolutely nothing with. I throw this out there because I don't know what field type your memo field really is.
Open parens are required! (double click another field to get the right format and then manually overtype to get your field name for the memo field.)
SQL Server syntax
(Cast Table."MyMemoField" as varchar(20))
Also little tip, you can do case when's in SQL Expressions and that is not documented either!
(case when table.field = 'Y' then 'abc' else 'def' end)
Just make sure to wrap in parens
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Thread starter
- #6
Rosemary,
Thanks for your response.
The database is Oracle. I use the Oracle ODBC driver but also have the Microsoft ODBC for Oracle driver available.
When I bring the table with the memo field into Crystal I can't even Browse the paticular field I am trying to extract, so I don't know how to give you any more information as to what "type" of memo field it is.
Because the db is on Oracle are you saying I am not going to be able to extract any memo-type data?
Hillary
Thanks for your response.
The database is Oracle. I use the Oracle ODBC driver but also have the Microsoft ODBC for Oracle driver available.
When I bring the table with the memo field into Crystal I can't even Browse the paticular field I am trying to extract, so I don't know how to give you any more information as to what "type" of memo field it is.
Because the db is on Oracle are you saying I am not going to be able to extract any memo-type data?
Hillary
Dear Hillary,
No, I am saying that you will have more luck if it is in Oracle.
The memo field may not display in the list of available fields, but you can manually type in.
Here is an example from another thread I answered:
(Cast(Rtrim(SUBSTR(PRO_WARRANTS."NARRATIVE", INSTR(PRO_WARRANTS."NARRATIVE",'[',1,1),( INSTR(PRO_WARRANTS."NARRATIVE",']'))-(INSTR(PRO_WARRANTS."NARRATIVE",'['))+1)) as varchar(250)))
As you can see that is way more complicated than what you are looking for so:
(Cast(MyTable."MyManuallyTypedField" as varchar(20))
See if that works for you. I am now on my home machine and cannot test against Oracle, but it looks ok to me.
Some tips:
Placing the memo field on the report canvas (you can suppress) often makes the SQL Expression work, I don't know why!
Enclosing the cast in () also increases chances for success!
Testing in a query tool first and then pasting into the SQL Expression window, increases chances that it will work.
Crystal is very picking in the SQL Expression editor an often as soon as you get an error - even if later the statement is correct - it will still throw errors.
Let me know if you have any other issues or need more assistance.
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
No, I am saying that you will have more luck if it is in Oracle.
The memo field may not display in the list of available fields, but you can manually type in.
Here is an example from another thread I answered:
(Cast(Rtrim(SUBSTR(PRO_WARRANTS."NARRATIVE", INSTR(PRO_WARRANTS."NARRATIVE",'[',1,1),( INSTR(PRO_WARRANTS."NARRATIVE",']'))-(INSTR(PRO_WARRANTS."NARRATIVE",'['))+1)) as varchar(250)))
As you can see that is way more complicated than what you are looking for so:
(Cast(MyTable."MyManuallyTypedField" as varchar(20))
See if that works for you. I am now on my home machine and cannot test against Oracle, but it looks ok to me.
Some tips:
Placing the memo field on the report canvas (you can suppress) often makes the SQL Expression work, I don't know why!
Enclosing the cast in () also increases chances for success!
Testing in a query tool first and then pasting into the SQL Expression window, increases chances that it will work.
Crystal is very picking in the SQL Expression editor an often as soon as you get an error - even if later the statement is correct - it will still throw errors.
Let me know if you have any other issues or need more assistance.
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Thread starter
- #8
When I run the formula as-is I get a Missing ) error and the cursor inserts before Cast. I have inserted a ) at the end and after Cast but I keep getting a ) error. If I take away the ( before Cast then Crystal gives me an error that the remaining text does not appear to be part of the formula and the cursor inserts before Cast. The word Cast is not in blue.
I am using Crystal Syntax and CR 8.5
When I change the formula editor to Basic Syntax it responds, "A statement is expected here." Again, the cursor inserts before Cast.
I'm not sure what is going on but your help is appreciated!
Thanks,
Hillary
I am using Crystal Syntax and CR 8.5
When I change the formula editor to Basic Syntax it responds, "A statement is expected here." Again, the cursor inserts before Cast.
I'm not sure what is going on but your help is appreciated!
Thanks,
Hillary
synapsevampire
Programmer
Ro is dead on, of course, she did omit a paren though:
(Cast(MyTable."MyManuallyTypedField" as varchar(20)))
Not sure about having the extra parens around the whole statement, it should work either way, but Crystals kinda funny...
-k kai@informeddatadecisions.com
(Cast(MyTable."MyManuallyTypedField" as varchar(20)))
Not sure about having the extra parens around the whole statement, it should work either way, but Crystals kinda funny...
-k kai@informeddatadecisions.com
Yes, I see in my haste I left off a parens thanks for coming to the rescue SV.
It may just be SQL Server, but if I put a Cast or Case when without the open and close paren - it bombs.
The interesting thing is that the db app I consult on also has the ability to add expressions (they call them calculated fields) and there is the same exact behaviour. Works with the parens - error city without.
Hopefully, it is now working for you Hillary.
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
It may just be SQL Server, but if I put a Cast or Case when without the open and close paren - it bombs.
The interesting thing is that the db app I consult on also has the ability to add expressions (they call them calculated fields) and there is the same exact behaviour. Works with the parens - error city without.
Hopefully, it is now working for you Hillary.
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Thread starter
- #11
It doesn't matter if I put the ) at the end. It still throws the curser between ( and Cast and gives me a missing ) operator.
I'm pretty sure the problem is with the word "Cast" and "varchar". Everytime I put an operator in the Formula editor it turns blue. Neither of these words turn blue.
I'm not sure what I'm missing, but here are my specs...
Oracle database
Oracle ODBC Driver
CR 8.5.0.217
I'm in the Formula Editor using Crystal Syntax
Thank you very much for your continued help!
Hillary
I'm pretty sure the problem is with the word "Cast" and "varchar". Everytime I put an operator in the Formula editor it turns blue. Neither of these words turn blue.
I'm not sure what I'm missing, but here are my specs...
Oracle database
Oracle ODBC Driver
CR 8.5.0.217
I'm in the Formula Editor using Crystal Syntax
Thank you very much for your continued help!
Hillary
Please post your formula here.
Cut and paste here:
Also, what is the exact field type. Do you have an Oracle DBA? Crystal lumps any field greater than 254 as a memo. But, there are db rules you cannot break. An Oracle field of Long can hold 2 GB of data and cannot be converted or used in a substring.....etc.
Also, the words are not recognized for me either "not blue" but I use these expressions on a daily basis in my reports.
I don't know if this this is the case or not. I know that the statement on a regular field - works so it is hard to figure out why it is not working for you.
I wont be able to check back in til I hit my hotel tonight - but hand in there...we will hopefully figure out.
ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
Cut and paste here:
Also, what is the exact field type. Do you have an Oracle DBA? Crystal lumps any field greater than 254 as a memo. But, there are db rules you cannot break. An Oracle field of Long can hold 2 GB of data and cannot be converted or used in a substring.....etc.
Also, the words are not recognized for me either "not blue" but I use these expressions on a daily basis in my reports.
I don't know if this this is the case or not. I know that the statement on a regular field - works so it is hard to figure out why it is not working for you.
I wont be able to check back in til I hit my hotel tonight - but hand in there...we will hopefully figure out.
ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Thread starter
- #13
Crystalguru
Technical User
Hillary,
You need to be in the SQL Expression Editor NOT the formula editor for the formula to work.
You need to be in the SQL Expression Editor NOT the formula editor for the formula to work.
Dear CrystalGuru,
Good pick up there ... I assumed she was in the SQL Expression Formula Editor... Thanks,ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
Good pick up there ... I assumed she was in the SQL Expression Formula Editor... Thanks,ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question
- Locked
- Question