2 memo fields in a crystal report

[rainbowrabbit]

Crystal Reports vr9
Oracle 9.2.0.6.0

Using oracle drivers to connect not ODBC

I have a table which contains all memo's recorded against records in the database called memo_str. I have this table in a report twice, once as an alias, as I need to get different memo data onto a report for different groups in the report.

It works fine until I place the second memo field on the report. I then get an ORA-3127 error and the whole report siezes up! I have to restart crystal reports completely to get back in.

I managed to do this link in a crystal 8.5 report ok. Is there a workaround or is it a case of creating a view with text cut into manageable pieces using substr??

 

[Turkbear]

Hi,
That error is odd, since it does not appear to have anything to do with what you are attempting but see after the code for a posible connection)

ORA-03127 no new operations allowed until the active operation ends

Cause: An attempt was made to execute a new operation before the active non-blocking operation completed or a new operation was attempted before all the pieces of a column were inserted or fetched.

Action: Execute the new operation after the non-blocking operation completes. If piecewise binds/defines were done, execute the new operation after all the pieces have been inserted or fetched.

What is the datatype of memo_str?
The alias is just a renamed version of the first table, so the fields will have identical data for each record..how can there be a different memo_str value for the same record..If a different record, why the alias?
I suspect the attempt to read the data twice may happen too close in time so that the forst memo_)str value is not fully returned before a second request i smade for that same data.







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rainbowrabbit]

Hi Turkbear,

The data type is long.

The reason for the alias is that there are many different entity types that can have a memo recorded against them i.e. people, students, buildings, transport routes etc etc etc.

If you have a report that has 2 entity types that have memo data recorded against them I just create an alias.

I have also discovered I can't split the data out in SQL using substr(memo_data,1,256) as the data type is long (I am using PL/SQL developer version 6.06).

 

[Turkbear]

Hi,
There are, as you have discovered, serious limitations when working with LONG data types ( that is why Oracle wants us to use LOBs ( CLOB,BLOB) )..

I still do not understand the need for an alias..If you group by Entity Type and place the memo_data field in the detail of that group, each entity that your select criteria returns will have its 'own' memo data displayed.

What in your report needs, and/or data model have I missed?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[rainbowrabbit]

Sorry Turkbear I haven'r really explained myself on the need for an alias.

The database is an educational database. Say you have a particular Bus Route that is created on the database. The route is saved in the route table. A memo of type long can be recorded against that route, which is stored in the memo_str table, with information about the driver of the route or the vehicle type for example. When the route is created students could be associated with that route.

Students can also have memo of type long recorded against them which is also recorded in the memo_str table. This information is about the student not the route. The student records are saved in the student table.

The memo_str table has a unique id against each memo. In this case you may have 1 as the id for the route and 2,3,4,5 for the memo's recorded against each student.

The report first groups by the route_id of the route table and then the student_id of the student table.

If you want to get the memo's for both the route and the students you have touse an alias, which is where it all goes wrong!

 

[rainbowrabbit]

Sometimes you can't see for looking.

I have found a workaround by using a humble subreport. Still it is strange that you can't get 2 different memo's on the main report but it's ok if you use a subreport? Especially as you could do it in Crystal 8.5

 

[sttngds9]

I managed to do this link in a crystal 8.5 report ok. Is there a workaround or is it a case of creating a view with text cut into manageable pieces using substr?? "

How you managed to make link with memo fields in Crystal 8.5 ?