Maximo Open Forum

 View Only

Related Content

Back to discussions
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
    ------------------------------

    Original Message


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

    Original Message


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

    Original Message


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

    Original Message


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

    Original Message


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

    Posted 07-25-2024 11:56

    As Gail said, the preferred approach is to determine what makes that group of Locations unique and try to build a sub-query that finds them without using all those LIKE phrases matching the Location.  For one thing, searching with a LIKE that has the "%" on both ends is very likely to include Locations that were not expected.

    However, if you're in a rush to do that, it appears that your query is made up of 2 parts that each includes the same checks for Locations but has different checks for the WO.

    One solution is to combine the 2 parts to eliminate the duplication so that it looks something like this:

    (((WOClass = 'WORKORDER' or WOClass = 'ACTIVITY') and SiteID = 'JAXWEST'  and IsTask = 0 and ((Status NOT IN ('COMP','CLOSE','CAN') and HistoryFlag = 0) or (Status IN ('COMP', 'CLOSE') 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))))



    ------------------------------
    Julio Hernandez
    Ergonare, Inc.
    ------------------------------



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

    Posted 07-25-2024 13:55

    Man, I LOVE this GROUP!! Thank you all so much for your thoughtful feedback and suggestions! What a powerful force we are when we work together!

    Julio - that's exactly the solution that I was looking for, but just didn't know how to make happen. I do appreciate the other responses though, and have taken nuggets from each of them that will help me in the future.

    Thanks again all!

    Amy



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

    Original Message


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

    Posted 07-28-2024 19:24

    Hi Amy,

    I see that people are jumping straight in to simplifying the SQL which I would too, but I need to ask something first.

    You've stated that your searching for locations that contain the value, but what eveyone has not asked is "what is the structure of you location coding?".

    I ask this because I'm not assuming that the values should be a contains statement. Can you please give samples of the locations that you are looking for?  Is it really that 124 any of these example locations:

    • 124
    • 1245
    • 01245
    • 4646124
    • 1599124ABCD

    The answer you give changes my response.  Please note that using the % negates the benefit of having indexes.

    I do challenge the two parts in your statement

    1. The first part is looking at:
      1. Work orders and activity work orders
      2. Status not in('COMP','CLOSE','CAN')
      3. Historyflag = 0
      4. Siteid = 'JAXWEST'
      5. istask = 0
      6. A whole bunch of locations using the like statement
    2. The second part (or) is looking at:
      1. Work orders and activity work orders
      2. Status = 'CLOSE' or status = 'COMP'
      3. Historyflag = 0
      4. Siteid = 'JAXWEST'
      5. istask = 0
      6. statusdate >= getdate()-14) 
      7. A whole bunch of locations using the like statement.
    • 2.1 can be compressed to status in ('COMP','CLOSE')
    • The SQL does not meet the criteria.
      • You should write this against the WOSTATUS table because when a work order change the status, the status date is updated.
      • You have not restricted the query to the locations created in the last six months, instead you have manually added all the locations.  Try referring to the locations via the LOCSTATUS table.
    • You have not future proofed the status i.e. synonyms.  Please refer to the WOSTATUS domain.
    • You do not need historyflag = 0 as you are restricting the work orders to a defined list of statuses.

    So, I said I won't jump in, but looking at the criteria in essence the framework looks more like:

    (locations exists (select 1 from locstatus where locstatus.location = workorder.location and locstatus.siteid = workorder.siteid and changedate between add_months(changedate,-6) and getdate()) and status in (select value from synonym where domainid = 'LOCASSETSTATUS' and status = 'OPERATING')

    and

    wonum exists (select 1 from wostatus where wostatus.wonum = workorder.wonum and wostatus.siteid = workorder.wonum and status in (select value from synonymdomain where domainid = 'WOSTATUS' and maxvalue in ('CLOSE','COMP))

    and 

    istask = 0



    ------------------------------
    Craig Kokay
    Principal Consultant
    COSOL

    email: craig.kokay@cosol.global
    #IBMChampion
    ------------------------------



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

    Posted 04-11-2025 13:00

    Thanks for your input, Craig! I'm sure that any errors or inefficiencies in my code example are entirely due to my limited skills in writing SQL queries. :) I appreciate your suggestions and will study them and the other replies from the talented members of this forum to improve my efforts going forward.

    Cheers,
    Amy 



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

    Original Message


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

    Posted 04-14-2025 15:17

    The others are right on. One possible way is to create a view with that ugly SQL and make it Maximo aware. 

     Did you supply an example of your locations that makes it necessary for the values to be wrapped with % wildcards?



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



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

    Posted 04-14-2025 15:30

    Hi Wes,

     

    I did not, but I think we figured it out, using the suggestions from the other kind folks that responded.

     

    Thanks for your reply,

    Amy

     




    Original Message