Maximo Open Forum

Sid Ogden posted 01-26-2023 09:02

LONGDESCRIPTION is its own table. Because of this, there is a Maximo relationship established for it on any table that has an attribute with a long description. They are linked by the unique identifier of the record in the table (for PM this is pmuid) to the LDKEY on LONGDESCRIPTION.

Try this in your where clause:

siteid = 'WODM'
and pmuid in (select ldkey from longdescription
where ldownertable = 'PM')

Amy Tatum posted 01-26-2023 09:11

DESCRIPTION_LONGDESCRIPTION is actually an alias for the field, so doesn't work in a query.  You can add the long description field to your Advanced Search and query from there, or if you stick with the where clause you would need to look to the LONGDESCRIPTION table.  As noted by Sid, the join is on the pmuid and ldkey; the actual text would be in the LDTEXT field if you were searching for specific strings instead of not null.  You may also want to add LDOWNERCOLUMN = 'DESCRIPTION' to Sid's suggestion if there may be long descriptions on multiple PM fields and you want to narrow to just the description field.

Jason Verly posted 01-27-2023 10:05

I'll add another version of the query to the one @Sid Ogden gave (which is absolutely correct) with one that uses the EXISTS statement:

siteid = 'WODM' and
exists(select null from longdescription where ldkey=pm.pmuid and ldownertable='PM' 
and ldownercol='DESCRIPTION' and ldtext is not null)​

The reason I'd suggest using EXISTS vs IN is the performance impact between the two methods.

The general rule is I've been told over the years is:

• ​Use EXISTS when sub-querying on a large data set. 
• Use IN when sub-querying on a small data set. 

The problem for most people when running queries is typically "we don't know what we don't know" on the dataset. So by default I've taught my Maximo power users to use EXISTS as the safest syntax choice when you don't know how much information you're searching against.