Maximo Open Forum

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 

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? :(

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 

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:
Sent: 07-24-2024 15:00
From: Amy Tellier-Briggs
Subject: Increase size of query character limit in Work Order Tracking?

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

Original Message:
Sent: 07-24-2024 14:54
From: Amy Tellier-Briggs
Subject: Increase size of query character limit in Work Order Tracking?

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

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:
Sent: 07-25-2024 09:20
From: Will Zurkan
Subject: Increase size of query character limit in Work Order Tracking?

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:
Sent: 07-24-2024 15:00
From: Amy Tellier-Briggs
Subject: Increase size of query character limit in Work Order Tracking?

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

Original Message:
Sent: 07-24-2024 14:54
From: Amy Tellier-Briggs
Subject: Increase size of query character limit in Work Order Tracking?

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

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 

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.

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 

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 

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.

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 

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 

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

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 

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 

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

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 

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 

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?

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