Sample Advanced Filters

Below are several sample advanced filters. Note that the syntax used to join tables differs for advanced filters used in Lucity Desktop and Lucity Web.

Select all Work Orders with a TASK CODE = 'EVNINT01'

• WKORDER WHERE WO_ID IN (SELECT WT_WO_ID FROM WKWOTSK WHERE WT_TASK_CD='EVNINT01')

Select all Work Orders with an EMPLOYEE resource named 'CHARLIE BROWN'

• WKORDER WHERE WO_ID IN (SELECT WR_WO_ID FROM WKRESRCE WHERE WR_RTYP_CD=1 AND WR_RSRC_TY='CHARLIE BROWN')

Select all Work Orders with an EQUIPMENT resource named '2006 DODGE RAM'

• WKORDER WHERE WO_ID IN (SELECT WR_WO_ID FROM WKRESRCE WHERE WR_RTYP_CD=2 AND WR_RSRC_TY='2006 DODGE RAM')

Select all Work Orders with a MATERIAL resource named 'Aerosol Paint can'

• WKORDER WHERE WO_ID IN (SELECT WR_WO_ID FROM WKRESRCE WHERE WR_RTYP_CD=3 AND WR_RSRC_TY='Aerosol Paint can')

Select all Work Orders with a FLUID resource named '10W-30'

• WKORDER WHERE WO_ID IN (SELECT WR_WO_ID FROM WKRESRCE WHERE WR_RTYP_CD=4 AND WR_RSRC_TY='10W-30')

Select all Work Orders with a CONTRACTOR resource named 'TODDS TOWING'

• WKORDER WHERE WO_ID IN (SELECT WR_WO_ID FROM WKRESRCE WHERE WR_RTYP_CD=5 AND WR_RSRC_TY='TODDS TOWING')

Select all Work Orders with a 'WATER HYDRANT' Asset that has a Common ID of 5261.

• WKORDER WHERE WO_ID IN (SELECT AS_WO_ID FROM WKWOASSET WHERE AS_CAT_INV='12' AND AS_LINK1='5261')

  Note: An AS_CAT_INV of 12 represents 'Water Hydrants.' These numbers can be found in WKCATINV.

Select all Fleet out of service (by being on a Work Order with a status of either 104-Fleet On-Going, 105-Fleet Waiting On Parts, or 106-Fleet Waiting On Vendor).

• EFFLEET WHERE (EFFLEET.FL_ID) IN (SELECT AS_INV_ID FROM WKWOASSET WHERE (WKWOASSET.AS_WO_ID IN (SELECT WO_ID FROM WKORDER WHERE (WKORDER.WO_STAT_CD > 103 AND WKORDER.WO_STAT_CD < 107) AND (WKORDER.WO_INV_ID=32))))

Find all Work Orders that do not have an Asset.

• WKORDER WHERE WO_ID NOT IN (SELECT AS_WO_ID FROM WKWOASSET)