• Search Forums
    Advanced Search Topics Posts
Prev
Next
Forums Performance Management Lawson Business Intelligence/Reporting/Crystal

Reporting on LTM CLOB field

 5 Replies
 0 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
S Eckerson
Basic Member Send Private Message
Posts: 7
Basic Member
3/16/2012 8:11 PM
The users have requested that the comments that are stored in the auditlog field from LTM.workassignment be included in a Crystal Report. Attempts from SQL or Crystal Reports to extract the information needed from the CLOB field have been unsuccessful. Has anyone out there found the key to extracting the data? Thanks for any and all ideas!
Chris Martin
Veteran Member Send Private Message
Posts: 277
Veteran Member
3/16/2012 8:44 PM
Is LTM on Oracle or SQL Server?
S Eckerson
Basic Member Send Private Message
Posts: 7
Basic Member
3/16/2012 8:58 PM
Oracle
Chris Martin
Veteran Member Send Private Message
Posts: 277
Veteran Member
3/16/2012 9:09 PM
Try something along these lines in a sql query:

select dbms_lob.substr( fieldname, 4000, 1 ) from table_name
S Eckerson
Basic Member Send Private Message
Posts: 7
Basic Member
4/16/2012 10:42 PM
I opened a CASE ticket with Lawson regarding the need to get information from this field. There is no way to report from the CLOB at this time. The dbms_lob.substr function does work, except that the CLOB fields are much larger than the 4000 bytes allowed in the function.

Here is the solution...the comments I want to show on the report are in an LTM table, the TURNOVERDATA table. In fact, just about everything I needed for the report is in the TURNOVERDATA table. My current report was from data in S3 that required really complex (but fun to write!) SQL.

Thank you for the replies.
Nabil
Veteran Member Send Private Message
Posts: 61
Veteran Member
9/13/2012 6:58 PM
We were able to report on the POSITION Clob fields using something like this:
cast(substr(POSITION.OTHERINFORMATION,1,32000) as varchar(32000)) as OTHERINFORMATION

We have DB2