Sample Advanced Filters

We've provided several sample Advanced Filters below. Note that the difference between advanced filters used in the Desktop application and Web application is in the syntax used for joining tables. Web application filters should use the INNER JOIN syntax as shown in these examples:

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

  WKORDER INNER JOIN WKWOTSK ON WKORDER.WO_ID = WKWOTSK.WT_WO_ID

  WHERE WT_TASK_CD='EVNINT01'

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

  WKORDER INNER JOIN WKRESRCE ON WKORDER.WO_ID = WKRESRCE.WR_WO_ID AND WR_RTYP_CD=1

  WHERE WR_RSRC_TY='CHARLIE BROWN'

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

  WKORDER INNER JOIN WKRESRCE ON WKORDER.WO_ID = WKRESRCE.WR_WO_ID AND WR_RTYP_CD=2

  WHERE WR_RSRC_TY='2006 DODGE RAM'

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

  WKORDER INNER JOIN WKRESRCE ON WKORDER.WO_ID = WKRESRCE.WR_WO_ID AND WR_RTYP_CD=3

  WHERE WR_RSRC_TY='Aerosol Paint can'

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

  WKORDER INNER JOIN WKRESRCE ON WKORDER.WO_ID = WKRESRCE.WR_WO_ID AND WR_RTYP_CD=4

  WHERE WR_RSRC_TY='10W-30'

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

  WKORDER INNER JOIN WKRESRCE ON WKORDER.WO_ID = WKRESRCE.WR_WO_ID AND WR_RTYP_CD=5

  WHERE WR_RSRC_TY='TODDS TOWING'

• Select all Work Orders with a 'WATER HYDRANT' Asset that has a Common ID of 5261. Note, the AS_CAT_INV of 12 represents 'Water Hydrants'. These numbers can be found in WKCATINV.

  WKORDER INNER JOIN WKWOASSET ON WKORDER.WO_ID = WKWOASSET.AS_WO_ID

  WHERE AS_CAT_INV='12' AND AS_LINK1='5261'

• Select all Work Orders with a 'SEWER PIPE' Asset that has an Upstream ID of TEST1 and a Downstream ID of TEST2. Note, the AS_CAT_INV of 6 represents 'Sewer Pipe'. These numbers can be found in WKCATINV

  WKORDER INNER JOIN WKWOASSET ON WKORDER.WO_ID = WKWOASSET.AS_WO_ID

  WHERE AS_CAT_INV='6' AND AS_LINK1='TEST1' AND AS_LINK2 = 'TEST2'