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?

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?

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

Original Message:
Sent: Tue October 22, 2024 07:49 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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
------------------------------

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

Original Message:
Sent: Wed October 23, 2024 02:56 AM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Tue October 22, 2024 07:49 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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
------------------------------

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

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

Original Message:
Sent: Wed October 23, 2024 02:56 AM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Tue October 22, 2024 07:49 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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
------------------------------

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

Original Message:
Sent: Wed October 23, 2024 03:18 PM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 10:52 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 02:56 AM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Tue October 22, 2024 07:49 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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
------------------------------

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

Original Message:
Sent: Wed October 23, 2024 03:24 PM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 03:18 PM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 10:52 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 02:56 AM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Tue October 22, 2024 07:49 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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
------------------------------

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

Original Message:
Sent: Wed October 23, 2024 03:24 PM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 03:18 PM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 10:52 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 02:56 AM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Tue October 22, 2024 07:49 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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
------------------------------

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

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

Original Message:
Sent: Wed October 23, 2024 03:24 PM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 03:18 PM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 10:52 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 02:56 AM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Tue October 22, 2024 07:49 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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
------------------------------

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

"

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

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

Original Message:
Sent: Wed October 23, 2024 03:24 PM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 03:18 PM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 10:52 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 02:56 AM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Tue October 22, 2024 07:49 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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
------------------------------

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

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

"

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

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

Original Message:
Sent: Wed October 23, 2024 03:24 PM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 03:18 PM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 10:52 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 02:56 AM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Tue October 22, 2024 07:49 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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
------------------------------

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.

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

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

"

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

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

Original Message:
Sent: Wed October 23, 2024 03:24 PM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 03:18 PM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 10:52 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Wed October 23, 2024 02:56 AM
From: Darren Hornidge
Subject: How to query work order long description field with SQL?

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

Original Message:
Sent: Tue October 22, 2024 07:49 AM
From: Christopher Stewart
Subject: How to query work order long description field with SQL?

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
------------------------------

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

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?

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?

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

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?

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?

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?