Prev
Next
Forums Lawson Business Intelligence/Reporting/Crystal

Reporting on LTM CLOB field

Sort:
You are not authorized to post a reply.
Author
Messages
S Eckerson
Basic Member
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
    Posts: 277
    Veteran Member
    3/16/2012 8:44 PM
      Is LTM on Oracle or SQL Server?
      S Eckerson
      Basic Member
      Posts: 7
      Basic Member
      3/16/2012 8:58 PM
        Oracle
        Chris Martin
        Veteran Member
        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
          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
            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
              You are not authorized to post a reply.