Maximo Open Forum

 View Only
Expand all | Collapse all

Increase size of query character limit in Work Order Tracking?

  • 1.  Increase size of query character limit in Work Order Tracking?

    Posted 07-24-2024 14:55

    Folks - I'm trying to create a query in Work Order Tracking that has a lot of individual locations in it, and I keep running into an error that says I've exceeded the 4,000 character limit for a query. Does anyone know if there's a way to increase that limit? Otherwise, I need to figure out a way to give me the results I need and pare down the character count. I know I'm probably doing this the WAY hard way (I'm not an SQL expert, obviously!), so any other suggestions for cleaning up this code is greatly appreciated.

    The client wants a report that shows all work orders for all these locations created in the last 6 months, but ONLY include COMP & CLOSE WO's whose status was changed to COMP or CLOSE in the last 14 days. Here's what I came up with that's too large for Maximo to accept as a where clause:

    (((woclass = 'WORKORDER' or woclass = 'ACTIVITY')  and (status not in('COMP','CLOSE','CAN')) and historyflag = 0  and siteid = 'JAXWEST'  and istask = 0) and (exists (select 1  from multiassetlocci  where (((location like '%102%' or location like '%103%' or location like '%1040%' or location like '%1047%' or location like '%1048%' or location like '%1063.1%'  or location like '%1063.2%'  or location like '%1064%'  or location like '%1099%' or location like '%110%' or location like '%1111%' or location like '%1112%' or location like '%1113%' or location like '%1114%' or location like '%1115%' or location like '%1116%' or location like '%1120%' or location like '%1121%' or location like '%1136%' or location like '%1137%' or location like '%1138%' or location like '%1139%' or location like '%114%' or location like '%115%' or location like '%1160%' or location like '%1161%'or location like '%1162%'or location like '%1163%'or location like '%1165%'or location like '%1176%' or location like '%1177%' or location like '%1178%' or location like '%1179%' or location like '%118%' or location like '%1190%' or location like '%1191%'  or location like '%1192%'  or location like '%1193%'  or location like '%1194%'  or location like '%1195%'  or location like '%1196%'  or location like '%1197%'  or location like '%1198%'  or location like '%1199%'  or location like '%120%' or location like '%121%' or location like '%122%' or location like '%1230%' or location like '%1231%'  or location like '%1232%'  or location like '%1233%'  or location like '%1234%'  or location like '%1235%' or location like '%1236%'  or location like '%1237%' or location like '%1238%'  or location like '%1239%' or location like '%124%' or location like '%1250%' or location like '%1251%' or location like '%1252%' or location like '%1253%' or location like '%1254%' or location like '%1255%' or location like '%2031%' or location like '%2032%' or location like '%2033%' or location like '%2043%' or location like '%2045%' or location like '%2046%' or location like '%2047%' or location like '%2160%' or location like '%2161%' or location like '%2162%' or location like '%2220%' or location like '%2221%' or location like '%2222%' or location like '%2225%' or location like '%2230%' or location like '%2231%' or location like '%2232%' )))  and (recordkey=workorder.wonum  and recordclass=workorder.woclass  and worksiteid=workorder.siteid))) or

    ((woclass = 'WORKORDER' or woclass = 'ACTIVITY')  and (status = 'CLOSE' or status = 'COMP')  and siteid = 'JAXWEST'  and istask = 0  and statusdate >= getdate()-14) and (exists (select 1  from multiassetlocci  where (((location like '%102%' or location like '%103%' or location like '%1040%' or location like '%1047%' or location like '%1048%' or location like '%1063.1%'  or location like '%1063.2%'  or location like '%1064%'  or location like '%1099%' or location like '%110%' or location like '%1111%' or location like '%1112%' or location like '%1113%' or location like '%1114%' or location like '%1115%' or location like '%1116%' or location like '%1120%' or location like '%1121%' or location like '%1136%' or location like '%1137%' or location like '%1138%' or location like '%1139%' or location like '%114%' or location like '%115%' or location like '%1160%' or location like '%1161%'or location like '%1162%'or location like '%1163%'or location like '%1165%'or location like '%1176%' or location like '%1177%' or location like '%1178%' or location like '%1179%' or location like '%118%' or location like '%1190%' or location like '%1191%'  or location like '%1192%'  or location like '%1193%'  or location like '%1194%'  or location like '%1195%'  or location like '%1196%'  or location like '%1197%'  or location like '%1198%'  or location like '%1199%'  or location like '%120%' or location like '%121%' or location like '%122%' or location like '%1230%' or location like '%1231%'  or location like '%1232%'  or location like '%1233%'  or location like '%1234%'  or location like '%1235%' or location like '%1236%'  or location like '%1237%' or location like '%1238%'  or location like '%1239%' or location like '%124%' or location like '%1250%' or location like '%1251%' or location like '%1252%' or location like '%1253%' or location like '%1254%' or location like '%1255%' or location like '%2031%' or location like '%2032%' or location like '%2033%' or location like '%2043%' or location like '%2045%' or location like '%2046%' or location like '%2047%' or location like '%2160%' or location like '%2161%' or location like '%2162%' or location like '%2220%' or location like '%2221%' or location like '%2222%' or location like '%2225%' or location like '%2230%' or location like '%2231%' or location like '%2232%' )))  and (recordkey=workorder.wonum  and recordclass=workorder.woclass  and worksiteid=workorder.siteid))))

    Thanks in advance,

    Amy 


    #Reporting

    ------------------------------
    Amy Briggs
    Maintenance Planner
    The Jackson Laboratory
    Bar Harbor, ME
    207-288-6356
    ------------------------------


  • 2.  RE: Increase size of query character limit in Work Order Tracking?

    Posted 07-24-2024 15:00

    I should clarify - I am able to run this query, BUT when I try to save the query or create a report from it, ad hoc or scheduled, it won't let me. THAT"s when I get this message:

    BMXAA4049E - The value specified exceeds the maximum field length that is allowed for this attribute: Value = (((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and (status not in('COMP','CL..., Object Name=QUERY, Attribute Name=CLAUSE, Maximum Length=4,000.

    Help? :(



    ------------------------------
    Amy Briggs
    Maintenance Planner
    The Jackson Laboratory
    Bar Harbor, ME
    207-288-6356
    ------------------------------



  • 3.  RE: Increase size of query character limit in Work Order Tracking?

    Posted 07-25-2024 09:21

    Hi Amy. Have you tried using BIRT or Cognos to create the report? I've had queries as long as yours without an issue... wonder if this is complaining about a particular attribute (rather than the query itself) exceeding 4000 character limit. For example, we have work order descriptions that concatenate PM + Job Plan... this was raising an error with Work Order Descriptions exceeding 4000 character limit, but only when being saved... meaning they had to be manually truncated to resolve issue. Another possibility is to not use Ad Hoc or Where Clause but the interface itself... meaning select the locations manually from the interface and then add statusdate and other conditions once it runs. Having said that, Maximo may be configured uniquely for every instance and so this could've been put in place to prevent overtaxing Maximo CPU. ~Will 



    ------------------------------
    Will Zurkan
    Symbotic
    ------------------------------



  • 4.  RE: Increase size of query character limit in Work Order Tracking?

    Posted 07-25-2024 15:09
    Edited by Amy Tellier-Briggs 07-25-2024 15:14

    Hi Will - I am using BIRT to create the report, and I've never run into this before, so I was kind of at a loss. But Julio provided some elegant code that solved the issue, so I think I'm all set. For what it's worth, I was able to manually enter my code in the Where clause and get results, but the problem arose when I tried to save that query or create a report from the result set. It's weird...

    Thanks for being willing to help! :)

    Amy



    ------------------------------
    Amy Briggs
    Maintenance Planner
    The Jackson Laboratory
    Bar Harbor, ME
    207-288-6356
    ------------------------------



  • 5.  RE: Increase size of query character limit in Work Order Tracking?

    Posted 08-08-2024 11:14

    Create a view, then in Maximo db config add it, then use the view as your query.



    ------------------------------
    Wes Williams
    Northrop Grumman
    ------------------------------



  • 6.  RE: Increase size of query character limit in Work Order Tracking?

    Posted 07-25-2024 10:03

    if your locations are the actual locations you could also try to change your like statments moving it to an in statment.

    e.g.

    replace 

    location LIKE '%102%'
                                    OR location LIKE '%103%'
                                    OR location LIKE '%1040%'
                                    OR location LIKE '%1047%'
                                    OR location LIKE '%1048%'

    with 

    location in ('102','103','1040','1048')

    This would save some characters used in your query.



    ------------------------------
    Ryan Medernach
    MEG ENERGY
    ------------------------------



  • 7.  RE: Increase size of query character limit in Work Order Tracking?

    Posted 07-25-2024 14:20

    Hi Ryan,

    I wanted to do that, but could never get the right code, or get the code to work. I'll do a little more experimenting - thanks for the suggestion! :)

    -Amy



    ------------------------------
    Amy Briggs
    Maintenance Planner
    The Jackson Laboratory
    Bar Harbor, ME
    207-288-6356
    ------------------------------



  • 8.  RE: Increase size of query character limit in Work Order Tracking?

    Posted 07-25-2024 11:09

    I'd look for something else to query by.  Description keyword, parent location, system?  If there's no commonality there, perhaps create a classification or add a new spec if you're already using classifications to 'group' them and write your queries against the spec.



    ------------------------------
    gail smith
    chevron
    ------------------------------



  • 9.  RE: Increase size of query character limit in Work Order Tracking?

    Posted 07-25-2024 15:14

    Yes ma'am, I thought of that too! But nothing was consistent enough in the properties of these locations (some of these queries had 50-60 different ones!) that I could glom onto.

    I like your idea of creating a new spec or classification, like "Animal Room" or "Vivaria" or something common about them all. We don't use classifications much, but it might be something I need to explore more. Thanks for the suggestion!

    -Amy



    ------------------------------
    Amy Briggs
    Maintenance Planner
    The Jackson Laboratory
    Bar Harbor, ME
    207-288-6356
    ------------------------------



  • 10.  RE: Increase size of query character limit in Work Order Tracking?