I will try to layout the situation before I get to the error. I have this report which starts with making a hold file containing assets, their status (from another table), and locations.

I take that hold and join it with a work order table. That work order table will have a work log summary and work log details table joined to it.

I get all the records I should be getting in the original hold file. Now, not all of those records will have associated work orders (with work logs) with them. That says to me, Left outer join. When I left outer join the hold file (with all the assets) and the work order join group, with selecting a record from the work log details table, I get the following error:

0 NUMBER OF RECORDS IN TABLE= 28 LINES= 28
(FOC1400) SQLCODE IS 24801 (HEX: 000060E1)
: ORA-24801: illegal parameter value in OCI lob function
(FOC1407) SQL FETCH CURSOR ERROR. : LONGDESCRIPTION

So, I have left outers joining the asset hold, work order, log summary, and then log details. Here is where it is really tripping me up...

When I switch the join for the log summary to log details (not the one joining assets to work orders) to an inner join, I do not get an error. However, I only get records which are associated with a work log detail. (I do not get assets with statuses which do not have a work log record associated with them.)

If I remove the log details from the select list, leaving that last join as an outer, I get the results I want (minus the field I had to remove). I get ALL assets (with an associated work log summary or not). But, as soon as I add the log details field, I get the error.

I hope someone can decipher what I have tried to explain and come up with a suggestion.

Thanks

This message has been edited. Last edited by: Kerry,

quote:

LONGDESCRIPTION

Look at the field format in the master, perhaps it's too long, perhaps it's defined as a text field...

Also, why don't you do all the joins in one request instead of creating an intermediary hold file, this may be more efficient.

I had used the hold because when I originally wrote the report, I had two paths of joins off one table. I joined off the work order one path down through the asset to get that info and the other path down through the work log tables. When I tried selecting from both paths WebFOCUs got upset. I don't remember the error now but I had found someone's solution of using a hold.

As for the format, LONGDESCRIPTION is the table. The field in question here which I am pulling from the table and causing the error is shows "USAGE=TX50, ACTUAL=TX"

I hope this is the info you were asking for.

Thanks,
Craig

You may want to read about "Controlling the Mapping of Large Character Data Types" in the documentation:


Server Administration for UNIX, Windows, OpenVMS, i5/OS, and z/OS V7.6.1

Text fields do sometimes cause problems.

I would regenerate the metadata (master) by customizing the data type mappings for Longchar as Alpha - this is an option in the WebFOCUS Server Console Metadata 'Create Synonym' page.

As a quick test, BEFORE doing the above, try to make sure that the text field is the last field in the request - I believe, when working with text fields, you can only have one in the WebFOCUS request, and it has to be the last field.

This message has been edited. Last edited by: Francis Mariani,

quote:

Text fields do sometimes cause problems.

I would regenerate the metadata (master) by customizing the data type mappings for Longchar as Alpha - this is an option in the WebFOCUS Server Console Metadata 'Create Synonym' page.

You, my good man, are my hero of the day! I changed from TX to A2000 and everything is working like it should.

(fyi...putting it as the last field did not do it.)

Thank you!

There's a setting you can add to your edasprof.prf which will force the text fields to be alpha fields when the master files are created. Below is the setting for Oracle, it may differ a bit for a different type of database:
ENGINE SQLORA SET CONVERSION LONGCHAR TEXT