Maximo

Maximo

Come for answers, stay for best practices. All we're missing is you.

 View Only
Expand all | Collapse all

How to query work order long description field with SQL?

  • 1.  How to query work order long description field with SQL?

    Posted Tue October 22, 2024 07:49 AM

    Maximo 8.11, I am trying to query work orders and include the long description field. I see there is a table LONGDESCRIPTION but it doe snot look to contain any of the long description data, only what types of entities have the long description field. Is this possible? If so how is it done?



    ------------------------------
    Christopher Stewart
    ------------------------------


  • 2.  RE: How to query work order long description field with SQL?

    Posted Wed October 23, 2024 12:51 AM

    Data is in LDTEXT as clob.

    You need to use LDOWNERTABLE = WORKORDER and LDKEY = WORKORDERID



    ------------------------------
    Juris Flugins
    ------------------------------



  • 3.  RE: How to query work order long description field with SQL?

    Posted Wed October 23, 2024 02:56 AM

    Hi Chris,

    If I understand correctly, you just want to some SQL to find workorders where the long description field contains some text?

    If you are doing this against the database, you could use something like this.  In my example, I am looking for the text OIL

    SELECT wonum, description, ldtext
    FROM WORKORDER
    INNER JOIN longdescription ON longdescription.ldkey = workorder.workorderid and ldownertable = 'WORKORDER' 
    WHERE ((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = '21')
    and UPPER(longdescription.ldtext) LIKE '%OIL%' 

    Or, if you want to do this from the where clause in work order tracking, you could try this

    ((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = '21')
    and EXISTS (SELECT 1 
                FROM longdescription
                WHERE longdescription.ldkey = workorder.workorderid 
                and ldownertable = 'WORKORDER' 
                and UPPER(longdescription.ldtext) LIKE '%OIL%' 
                )

    Hopefully this helps you

    Daz



    ------------------------------
    Darren Hornidge
    Asset Care Systems Manager
    CCEP
    Melbourne VIC
    ------------------------------



  • 4.  RE: How to query work order long description field with SQL?

    Posted Wed October 23, 2024 10:53 AM
    Edited by Christopher Stewart Wed October 23, 2024 10:53 AM

    Thanks for the suggestion. This is what is confusing to me. I only see 6 records in my LONGDESCRIPTION table where I have 32,000 work orders with long descriptions. Where are the DESCRIPTION_LONGDESCRIPTION field values stored?



    ------------------------------
    Christopher Stewart
    ------------------------------



  • 5.  RE: How to query work order long description field with SQL?

    Posted Wed October 23, 2024 03:18 PM

    Howdy,

    Is the query you are using just SELECT * FROM longdescription and this only returns 6 rows?

    That would not make sense if you are saying you have 32,000 workorders and all of them have a populated long description (ldtext).

    Can you share the current SQL you are using that returns the 6 rows please.

    Daz



    ------------------------------
    Darren Hornidge
    Asset Care Systems Manager
    CCEP
    Melbourne VIC
    ------------------------------



  • 6.  RE: How to query work order long description field with SQL?

    Posted Wed October 23, 2024 03:24 PM

    Yes I am just doing select * from longdescription. I was wondering if they changed this with MAS 8 vs 7.6 or something?



    ------------------------------
    Christopher Stewart
    ------------------------------



  • 7.  RE: How to query work order long description field with SQL?

    Posted Wed October 23, 2024 03:34 PM
    Edited by Bartosz Marchewka Wed October 23, 2024 03:37 PM

    Hi @Christopher Stewart

    Please observe that in the result you have an error (please see the screen below). I recognised that issue and it's connected with SQL client and data in LDTEXT. Please execute for example statement SELECT COUNT(*) FROM LONGDESCRIPTION Please try to use another SQL Client. 



    ------------------------------
    Bartosz Marchewka
    IBM Maximo Consultant
    AFRY
    ------------------------------



  • 8.  RE: How to query work order long description field with SQL?

    Posted Thu October 24, 2024 04:26 AM

    Hi @Christopher Stewart,

    @Bartosz Marchewka is right, there is something wrong with SQL Client you're using.

    Below query should give you full list of LONGDESCRIPTION for WORKORDER:

    SELECT WORKORDER.WONUM, WORKORDER.STATUS, WORKORDER.SITEID, LONGDESCRIPTION.* 
    FROM LONGDESCRIPTION  INNER JOIN WORKORDER ON WORKORDER.WORKORDERID = LONGDESCRIPTION.LDKEY
    WHERE
    LDOWNERTABLE = 'WORKORDER'



    ------------------------------
    Piotr Ozaist
    ------------------------------



  • 9.  RE: How to query work order long description field with SQL?

    Posted Thu October 24, 2024 07:41 AM
    Edited by Christopher Stewart Thu October 24, 2024 07:51 AM

    Ahh I did not catch that. Thanks! Do you have a SQL client you recommend?

    EDIT: Actually looking at the error it says this: SQL Error [     ]: The statement or command failed because it requires functionality that is not supported on a read-enabled HADR standby database. Reason code = "3".. SQLCODE=-1773, SQLSTATE=     , DRIVER=4.31.10

    Maybe this is due to my privilege's with the Maximo DB. This is an IBM hosted system. 



    ------------------------------
    Christopher Stewart
    ------------------------------



  • 10.  RE: How to query work order long description field with SQL?

    Posted Thu October 24, 2024 07:50 AM

    Hi @Christopher Stewart,

    I assume that your Maximo (Manage) is using DB2. Could you please check if you are getting this king of error message when you are executing select statement:

    SQL Error: [jcc][t4][10379][11961][4.31.10] Client disconnect exception encountered: 
    The statement or command failed because it requires functionality that is not supported on a read-enabled HADR standby database.
    Reason code = "3".. SQLCODE=-1773, SQLSTATE=    , DRIVER=4.31.10 ERRORCODE=-4499, SQLSTATE=nul


    If yes then problem is not with the SQL Client as I thought but with DB2 setup and size od data. 

    We had similar issue with one of our customer and we received this feedback from IBM support:

    "

    XML and large object (LOB) data must be inline to be successfully queried.

    INLINE-LOBs: The data for an INLINE LOB column is contained in the base table. This feature is enabled through the INLINE LENGTH option on the CREATE TABLE statement or the ALTER TABLE statement. The limit for inline lobs is 32,673 bytes.

    (...)

    Please find information about inline lobs :

    https://www.ibm.com/docs/en/db2/11.5?topic=performance-inline-lobs-improve

    https://www.ibm.com/docs/en/db2/11.5?topic=tables-storing-lobs-inline-in-table-rows

    https://www.ibm.com/docs/en/db2/11.5?topic=aracp-admin-is-inlined-determine-if-data-is-inlined

    "



    ------------------------------
    Bartosz Marchewka
    IBM Maximo Consultant
    AFRY
    ------------------------------



  • 11.  RE: How to query work order long description field with SQL?

    Posted Thu October 24, 2024 07:53 AM

    Yup that is it Bartosz. Is this something I should submit a ticket on?



    ------------------------------
    Christopher Stewart
    ------------------------------



  • 12.  RE: How to query work order long description field with SQL?

    Posted Thu October 24, 2024 08:00 AM

    Hi,

    If IBM is maintaining your database then yes I suggest you to create ticket and describe issue.

    If not then, I think, you need to start discussion with the team that is responsible for your DB2.



    ------------------------------
    Bartosz Marchewka
    IBM Maximo Consultant
    AFRY
    ------------------------------



  • 13.  RE: How to query work order long description field with SQL?

    Posted Thu October 24, 2024 08:08 AM

    Thanks. Yes it is IBM. I will open a ticket.



    ------------------------------
    Christopher Stewart
    ------------------------------



  • 14.  RE: How to query work order long description field with SQL?

    Posted Wed March 19, 2025 09:32 AM

    Hi Darren,

    I've been struggling with this too.  Isn't LONGDESCRIPTION.LDTEXT a CLOB data type?  How can you run a string comparison on it and get it to work?



    ------------------------------
    BRIAN BULLA
    ------------------------------



  • 15.  RE: How to query work order long description field with SQL?

    Posted Wed March 19, 2025 10:33 AM

    It will depend on the context of the comparison.  If you are doing the comparison in SQL and are on Oracle you might want to look at the DBMS_LOB.COMPARE function.  

    https://docs.oracle.com/cd/F49540_01/DOC/server.815/a68001/dbms_lob.htm#1009047

    If you are using SQL Server, then it is more straight forward and you can just compare them directly as you would any other text field.

    If you are using an Mbo in an automation script then you can also do a straight forward compare, for example something like the following:

    mbo1.getString("DESCRIPTION_LONGDESCRIPTION") == mbo2.getString("DESCRIPTION_LONGDESCRIPTION")



    ------------------------------
    Jason VenHuizen
    Sharptree
    https://sharptree.io
    https://opqo.io
    ------------------------------



  • 16.  RE: How to query work order long description field with SQL?

    Posted Wed October 23, 2024 09:32 AM

    Hi Chris, 

    You can achieve this by following below

    1. Linking workorder and longdescription tables using the relationship workorder.workorderid = longdescription.ldkey and longdescription.ldownertable = 'WORKORDER'
    2. The longdescription text can be found in ldkey of longdescription table


    ------------------------------
    Tinnokesh AP
    ------------------------------



  • 17.  RE: How to query work order long description field with SQL?

    Posted Wed October 23, 2024 09:33 AM

    Hi Chris

    You can achieve this by following below:

    1. Linking workorder and longdescription tables using the relation longdescription.ldkey = workorder.workorderid and ldownertable = 'WORKORDER'
    2. The long description text can be found in ldtext column of longdescription table


    ------------------------------
    Tinnokesh AP
    ------------------------------