If by long field you mean memo or blob field, you cannot do this with crystal unless you are on V9. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com
Sorry for the lack of information the first time. I have a long/memo/blob text field in the database. I need to check for the pattern "CP/" at the beginning of the text. If that's found, then following that will be text between two brackets []. I need that text. Thanks again for your help.
Here is are SQL expression that I finally came up with that extracts what you are looking for! I have a memo field in my database called Incident.Incident Resolution that is a memo field so I used that in the examples.
(CRORA8 v3.6 driver CRORA815.dll)
I called this %GetName
This one gets the text in the brackets (it includes the brackets)
Cast(Rtrim(SUBSTR(Incident."Incident Resolution", INSTR(Incident."Incident Resolution",'[',1,1),( INSTR(Incident."Incident Resolution",']'))-(INSTR(Incident."Incident Resolution",'['))+1)) as varchar(250))
result: [Some Name]
Note: I couldn't get this to work until I cast it as a varchar!
This one tests for the existence of "CP\" in the beginning of the memo field
I appreciate all the work you've done on this, but I don't quite understand why it doesn't apply to my report. Even when using the driver you listed, I'm getting ODBC errors that I have invalid functions (Cast, Instr, and Substr are not recognized). So I tried just using the convert function to SQL_VARCHAR and I get an error that SQL_VARCHAR is not a valid type for the convert function (even though I'm looking at a web page which says it is).
If anyone sees a glaring mistake in something I've tried, please speak up. Thanks.
I was having issues doing this in the Sql Expressions, invalid field, syntax, blah blah. I tried all different Oracle drivers.
It was driving me nuts, I do this all the time in SQL Server, so I decided that I would work it til I solved it!
1. Make sure that your memo field is on the report. I don't know why that makes a difference, but it does.
2. Also, I wrote a Sql Query that had these exact expressions, ran the Query. When data was correctly retrieved, I copied the sql expression to Crystal.
None of them work? I will be happy to send you my report with Saved Data for you to look at if you want.
Also, post your expressions and I will see if there is an error that I can glean.
SQL_VARCHAR isn't a valid datatype in Oracle, is it?
Show the SQL Expression that you used, because Ro's syntax looks right to me.
Perhaps the way that you're creating the SQL Expression is the problem?
Crystal is very persnickety about creating SQL Expressions, once the syntax is wrong, I often have to leave the *^$)% SQL Expression and go back in again and ram it down it's beak again to get it to take.
I opened the SQL expression editor and create an expression called getComplaint. This is the text I pasted in it:
Cast(Rtrim(SUBSTR(PRO_WARRANTS."NARRATIVE", INSTR(PRO_WARRANTS."NARRATIVE",'[',1,1),( INSTR(PRO_WARRANTS."NARRATIVE",']'))-(INSTR(PRO_WARRANTS."NARRATIVE",'['))+1)) as varchar(250))
I just changed the incident field name to my field name. When I try to save it, I get an error for inconsistent data types and also for INSTR being an invalid function. I did make sure the narrative field is on the report itself.
Things to note: I've never used SQL expressions before in crystal, and I've only been using crystal for a very short time. I noticed that in the sql expression dialog box, I did not have the option to choose my field, I had to type it in manually. Also, the cast, substr and instr functions are not listed up top in the function names. It has CONVERT, SUBSTRING and LOCATE.
I know that they are not in there. But, it does work.
Crystal is very particular.
Do this.
Insert/Field Object/SQL Expression, then just insert a field that does exist.
What is the format?
Once you have that.
Delete the sql expression that is giving you the problem.
In notepad, copy my expressions in and then edit your field name (capitalization etc) so that it is formatted to match how a field looks when you insert it in the SQL Expression editor.
Once you have the text correct. Create a new sql expression and paste in the text.
Test and save. Let me know how it goes. Also, if you want I will be happy to email you my report with saved data so that you can look at it!
If I haven't said it already, thanks to both of you for all your time and help. I really do appreciate it.
I tried the INSTR('CP/-hello world','CP/',1,1) and it worked.
Then I tried just putting one of my varchar database fields in an SQL expression and this worked fine:
PRO_WARRANTS."EMP_LAST_NAME"
Then I changed the name of my field to the long field and it didn't work. I get "Error Compiling SQL Expression: invalid field found here" error:
PRO_WARRANTS."NARRATIVE"
Then just to make sure I didn't have a typo in the field name, I changed one letter and got a different error ORA-00904 invalid column name, so I know I'm typing it in correctly.
I didn't really expect this next thing to work, but I went back to the first example, removed the first string and double clicked on one of my database fields. I then changed the name of the field to the long field and this is what I ended up with:
Just a little reminder. Crystal is funny with the SQL Expressions, once they hose on Syntax, they don't seem to recover. I delete the expression and create a new one.
If your field is indeed an Oracle Long type, which is designed to hold up to 2 GB (Yikes!) of data, then this will never work as:
<snip>
Restrictions in the use of LONG and LONG RAW are: only one LONG column allowed per table, LONG columns cannot be indexed, and cannot appear in integrity constraints. Long columns cannot be used in WHERE, GROUP BY, ORDER BY, CONNECT BY clauses or with the DISTINCT operator in SELECT. They cannot be referenced by SQL functions such as SUBSTR or INSTR.
LONG columns cannot be used in the SELECT list of a subquery or queries combined by set operators (UNION, UNION ALL, INTERSECT, or MINUS). They cannot be used in expressions and cannot be referenced when creating a table with a query or inserting into a table with a query. A variable or argument of a PL/SQL program unit cannot be declared using LONG (same with variables in database triggers).
<end snip>
Now, what are your options. Talk to your Oracle DBA and see if he/she can help you out by creating a new field as a clob in the view you are working on and inserting the data from the Long field into the new lob field. (hopefully i have stated this correctly). If you explain your issue to your Oracle DBA they should be able to help you out.
This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
By continuing to use this site, you are consenting to our use of cookies.