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 SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
manipulating oracle long field
- Thread starter beckybear
- Start date
Dear BeckyBear,
Exactly what are you trying to do? There are quite a few things that can be done with memo fields by using SQL expressions.
In the example below, I want to locate the position were my text starts in the field:
{fn LOCATE('test',Table."Myblobfield"
}
The return from the above formula gives me the starting position of my text that is being searched in the string.
Give me an example of what you need to do and we will work from there.
ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
Exactly what are you trying to do? There are quite a few things that can be done with memo fields by using SQL expressions.
In the example below, I want to locate the position were my text starts in the field:
{fn LOCATE('test',Table."Myblobfield"
}The return from the above formula gives me the starting position of my text that is being searched in the string.
Give me an example of what you need to do and we will work from there.
ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Thread starter
- #4
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.
BTW - I'm using CR 8, Oracle 8.1.7
BTW - I'm using CR 8, Oracle 8.1.7
Dear BeckyBear,
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 called this %TestValue
INSTR(Incident."Incident Resolution",'CP/',1,1)
Result: 1 if true 0 if false.
So then you create this formula:
if {%TestValue} = 0 then {%GetName}
Hope that this helps, it was the best that I could come up with.
Ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
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 called this %TestValue
INSTR(Incident."Incident Resolution",'CP/',1,1)
Result: 1 if true 0 if false.
So then you create this formula:
if {%TestValue} = 0 then {%GetName}
Hope that this helps, it was the best that I could come up with.
Ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Thread starter
- #6
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.
If anyone sees a glaring mistake in something I've tried, please speak up. Thanks.
Dear Beckybear,
Don't give up.
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.
Let me know what I can do to help.
ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
Don't give up.
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.
Let me know what I can do to help.
ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
synapsevampire
Programmer
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.
<angelic lil smile>
-k kai@informeddatadecisions.com
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.
<angelic lil smile>
-k kai@informeddatadecisions.com
Dear k,
I couldn't have phrased it better myself!
ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
I couldn't have phrased it better myself!
ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Thread starter
- #10
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 appreciate everyone's help.
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 appreciate everyone's help.
synapsevampire
Programmer
Hmmmm, As Ro suggested earlier...simplify.
Try just placing the following:
INSTR(Incident."Incident Resolution",'CP/',1,1)
Does it error?
How about:
INSTR('CP/-hello world','CP/',1,1)
If the above takes, then it's likely how you're referencing your table/columns.
Sorry this is such a pain to work with, but once you start getting SQL Expressions to work, you'll increase your flexibility immensely.
-k kai@informeddatadecisions.com
Try just placing the following:
INSTR(Incident."Incident Resolution",'CP/',1,1)
Does it error?
How about:
INSTR('CP/-hello world','CP/',1,1)
If the above takes, then it's likely how you're referencing your table/columns.
Sorry this is such a pain to work with, but once you start getting SQL Expressions to work, you'll increase your flexibility immensely.
-k kai@informeddatadecisions.com
Dear BeckyBear,
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!
ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
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!
ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Thread starter
- #13
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:
INSTR(PRO_WARRANTS."NARRATIVE",'CP/',1,1)
When I try to save this, I get error ORA-00932 inconsistent data types.
I know the field is there because it's on my report right now just being printed and that part works fine.
ro - Yes please send the report to ms_b_bear@yahoo.com. Thanks.
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:
INSTR(PRO_WARRANTS."NARRATIVE",'CP/',1,1)
When I try to save this, I get error ORA-00932 inconsistent data types.
I know the field is there because it's on my report right now just being printed and that part works fine.
ro - Yes please send the report to ms_b_bear@yahoo.com. Thanks.
Dear BeckyBear,
No problem, emailing now.
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.
Good luck,
ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
No problem, emailing now.
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.
Good luck,
ro Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
synapsevampire
Programmer
Isn't a long field in Oracle a number?
Perhaps you mean a long raw?
I don't think that the Oracle INSTR works against the LONG Raw type, but I may be confused, it's been a few months since I worked with Oracle.
I don't have Oracle in front of me, perhaps you can cast a long raw, but I doubt it, it's usually used for photos, blobs, etc.
-k kai@informeddatadecisions.com
Perhaps you mean a long raw?
I don't think that the Oracle INSTR works against the LONG Raw type, but I may be confused, it's been a few months since I worked with Oracle.
I don't have Oracle in front of me, perhaps you can cast a long raw, but I doubt it, it's usually used for photos, blobs, etc.
-k kai@informeddatadecisions.com
- Thread starter
- #16
synapsevampire
Programmer
Ahhhhh, OK, it's been awhile.
I don't think that Oracle INSTR is supported with the LONG type.
-k kai@informeddatadecisions.com
I don't think that Oracle INSTR is supported with the LONG type.
-k kai@informeddatadecisions.com
Dear BeckyBear,
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.
Good Luck,
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
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.
Good Luck,
ro
Rosemary Lieberman
rosemary@microflo.com, Microflo provides expert consulting on MagicTSD and Crystal Reports.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question