@Vivek Nagre if this is a long term issue then you may just want to just modify the advanced search to add the laborcode there so the users don't need to edit the where clause:
They would also be able to select 1 or more laborcodes from the list to build their query.
This example returns a where clause in MAXDEMO of
((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'BEDFORD') and (exists (select 1 from dbo.labtrans where (((laborcode = 'BARRY' or laborcode = 'SMITH'))) and (refwo=workorder.wonum and siteid=workorder.siteid)))
If I add in the BOILER location, then the clause expands to
((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'BEDFORD') and (exists (select 1 from dbo.multiassetlocci where ((location = 'BOILER')) and (recordkey=workorder.wonum and recordclass=workorder.woclass and worksiteid=workorder.siteid)) and exists (select 1 from dbo.labtrans where (((laborcode = 'BARRY' or laborcode = 'SMITH'))) and (refwo=workorder.wonum and siteid=workorder.siteid)))
And if I add in the BOILER location in a hierarchy search, then the where clause becomes
((woclass = 'WORKORDER' or woclass = 'ACTIVITY') and historyflag = 0 and istask = 0 and siteid = 'BEDFORD') and (exists (select 1 from dbo.multiassetlocci where (exists (select 1 from dbo.locancestor where ((ancestor = 'BOILER')) and (location =multiassetlocci.location and systemid = ( select systemid from locsystem where primarysystem = 1 and siteid =multiassetlocci.siteid) and siteid=multiassetlocci.siteid))) and (recordkey=workorder.wonum and recordclass=workorder.woclass and worksiteid=workorder.siteid)) and exists (select 1 from dbo.labtrans where (((laborcode = 'BARRY' or laborcode = 'SMITH'))) and (refwo=workorder.wonum and siteid=workorder.siteid)))
The main point here is that the user does not write any of this structured query language; they just fill in the fields they want in the advanced search and hit find.
More examples here
------------------------------
Christopher Winston
Projetech Inc.
IBM Champion 2024
------------------------------
Original Message:
Sent: 07-12-2024 06:13
From: Vivek Nagre
Subject: Workorder list by external
woclass = 'WORKORDER' or woclass = 'ACTIVITY') and location='XXXX' and istask = 0 and siteid = 'XXXXXX' and wonum in (select refwo from labtrans where laborcode='XXXXXX')
------------------------------
Vivek Nagre
JLL
------------------------------
Original Message:
Sent: 07-12-2024 05:51
From: Silva testes2
Subject: Workorder list by external
Thanks, it works very well :)
One last question, I'm sorry. How could I combine this with "location"?
Thanks once again
Original Message:
Sent: 07-12-2024 05:26
From: Vivek Nagre
Subject: Workorder list by external
Hi Yes it can be filter by the labor code
woclass = 'WORKORDER' or woclass = 'ACTIVITY') and istask = 0 and siteid = 'XXXXXX' and wonum in (select refwo from labtrans where laborcode='XXXXXX')
------------------------------
Vivek Nagre
JLL