Reserved Words
Reserved words are used to add data to a filter based on when, and by whom, the filter is run.
Example: A user wants a filter of all the work that was completed in the last week. He creates a filter that says:
-
SELECT * FROM WKORDER WHERE WKORDER.WO_END_DT >= '6/18/2012' AND WKORDER.WO_END_DT <= '6/22/2012'
However, this query statement would be out of date the next week, requiring the user to adjust the dates.
So, the user rewrites the statement using the %PREVIOUSWEEK% reserved word:
SELECT * FROM WKORDER WHERE WKORDER.WO_END_DT = %PREVIOUSWEEK%
The new filter displays all work orders with an end date that falls within the seven days previous to it being run.
Important: When using reserved words, enter them in all uppercase letters and enclose them in percent symbols (%).
Available Reserved Words
The following is a list of all the reserved words available in EAM Web:
'%LOGGEDINUSERID%' - Returns the currently logged in user's Login ID.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
When user Josh runs the filter:
SELECT * FROM WKORDER WHERE WKORDER.WO_EMP_TY = '%LOGGEDINUSERID%'
It becomes:
SELECT * FROM WKORDER WHERE WKORDER.WO_EMP_TY = 'JOSH'
'%LOGGEDINEMPCODE%' - Returns the currently logged in user's Employee ID, which is found in the EM_EMPL_CD field in the WKUEMP table. When used in a filter, the system replaces the reserved word with the Work Employee Code of the currently logged in user.
Work module to use this string. Also, the logged in employee must have his or her Login ID associated with an Employee record in the Work Flow Setup > Employees module.
- An agency must own the![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
When user Josh runs the filter:
SELECT * FROM WKORDER WHERE WKORDER.WO_EMP_CD = '%LOGGEDINEMPCODE%'
It becomes:
SELECT * FROM WKORDER WHERE WKORDER.WO_EMP_CD = 'JoshL '
'%FISCALYEAR%' - Returns the Current Fiscal Year from the Work Planning and Budgeting > Fiscal Year module.
Work module to use this string.
- An agency must own the![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%FISCALYEAR%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '01/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '01/01/2015 12:00 AM'
'%CURRENTYEAR%' - Inserts the current calendar year.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTYEAR%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '2014-01-01' and EFEQUIP.GE_CUR_DT < '2015-01-01'
'%CURRENTQUARTER%' - Inserts the current quarter.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTQUARTER%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.GE_FL_ID = EFFLEET.FL_ID WHERE EFEQUIP.ge_cur_dt >= '04/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/01/2014 12:00 AM'
'%CURRENTMONTH%' - Inserts the current month.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTMONTH%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/01/2014 12:00 AM'
'%CURRENTWEEK%' - Looks at the current date and inserts the dates for the Sundays before and after the current date.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/29/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/06/2014 12:00 AM'
'%CURRENTWEEK1%' - Looks at the current date and inserts the dates for the Sundays before and after the current date. (Results are identical to those using the previous reserve word.)
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK1%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/29/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/06/2014 12:00 AM'
'%CURRENTWEEK2%' - Looks at the current date and inserts the dates for the Monday in the same calendar week and the next Monday.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK2%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/30/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/07/2014 12:00 AM'
In this example, the user runs the filter on a Monday; so, the system returns records from the "previous" Monday (12 a.m. that same day, 06/30) to 12 a.m. the following Tuesday,07/07 (which catches all of Monday, 7/6).
'%CURRENTWEEK3%' - Looks at the current date and inserts the dates for the Tuesday in the same calendar week and the next Tuesday.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK3%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/08/2014 12:00 AM'
'%CURRENTWEEK4%' - Looks at the current date and inserts the dates for the Wednesday in the same calendar week and the next Wednesday.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK4%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/02/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/09/2014 12:00 AM'
'%CURRENTWEEK5%' - Looks at the current date and inserts the dates for the Thursday in the same calendar week and the next Thursday.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK5%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/03/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/10/2014 12:00 AM'
'%CURRENTWEEK6%' - Looks at the current date and inserts the dates for the Friday in the same calendar week and the next Friday.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK6%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/04/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/11/2014 12:00 AM'
'%CURRENTWEEK7%' - Looks at the current date and inserts the dates for the Saturday in the same calendar week and the next Saturday.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTWEEK7%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '07/05/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/12/2014 12:00 AM'
'%CURRENTDAY%' - Inserts the current 24-hour period. This is a range from 12:00 AM of the current day to 12:00 AM of the next day.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Friday, 06/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%CURRENTDAY%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/27/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/28/2014 12:00 AM'
'%PREVIOUSYEAR%' - Looks at the current date and inserts January 1 of the previous year to January 1 of the current year.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Friday, 6/27/2014, a user runs the following filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%PREVIOUSYEAR%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '01/01/2013 12:00 AM' AND EFEQUIP.ge_cur_dt < '01/01/2014 12:00 AM'
'%PREVIOUSMONTH%' - Looks at the current date and inserts the first date of the previous month and the first date of the current month.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Friday, 6/27/2014, a user runs the following filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%PREVIOUSMONTH%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '05/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/01/2014 12:00 AM'
'%PREVIOUSWEEK%' - Looks at the current date and inserts the dates of the previous week, Sunday to Sunday.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Friday, 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%PREVIOUSWEEK%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/15/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/22/2014 12:00 AM'
'%PREVIOUSDAY%' - Inserts the day previous to the current day.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Friday, 6/27/2014, user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%PREVIOUSDAY%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/26/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/27/2014 12:00 AM'
'%FIRSTHALFOFTHEMONTH%' - Inserts the first half of the month (days 1-15).
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Friday, 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%FIRSTHALFOFTHEMONTH%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/01/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '06/16/2014 12:00 AM'
'%SECONDHALFOFTHEMONTH%' - Inserts the second half of the month (days 16-31).
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Friday, 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%SECONDHALFOFTHEMONTH%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/16/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/01/2014 12:00 AM'
'%ONEWEEKAHEAD%' - Inserts the current day, plus seven future days.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Friday, 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%ONEWEEKAHEAD%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/27/2014 12:00 AM' AND EFEQUIP.ge_cur_dt < '07/05/2014 12:00 AM'
'%TWOWEEKSAHEAD%' - Inserts the plus 14 future days.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Friday, 6/27/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%TWOWEEKSAHEAD%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt >= '06/27/2014 12:00 AM'' AND EFEQUIP.ge_cur_dt < ''07/12/2014 12:00 AM'
'%ONEWEEKAHEADANDBEFORE%' - Inserts code to encompass all previous time, plus one week in the future. For example, this string could be used to query for all existing open Work Orders, as well as those that are open/due in the next week.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Friday, 6/27/2014, the user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%ONEWEEKAHEADANDBEFORE%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt <= '07/05/2014 12:00 AM'
'%TWOWEEKSAHEADANDBEFORE%' - Inserts code to encompass all previous time, plus two weeks in the future.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt = '%TWOWEEKSAHEADANDBEFORE%'
It becomes:
SELECT * FROM EFEQUIP LEFT JOIN EFFLEET ON EFEQUIP.ge_fl_id = EFFLEET.fl_id WHERE EFEQUIP.ge_cur_dt <= '07/15/2014 12:00 AM'
'%LAST(X,Y)%' - Inserts a range of dates where the Last Date is calculated as the Current Date - X days and the First Date is calculated as the Last Date - Y days.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Friday, 6/27/2014, a user runs the filter:
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt = '%LAST(1,5)%'
It becomes:
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt >= '6/21/2014' AND WKORDER.wo_mod_dt <= '6/26/2014'
The current date is 6/27. The reserve word goes back 1 days to 6/26 to find the end of the date range. It then goes back 5 days to find the beginning of the date range, 6/21.
'%NEXT(X,Y)%' - Inserts a range of dates where the First Date is calculated as the Current Date + X days and the Last Date is calculated as the Current Date + Y days.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt = '%NEXT(0,10)%'
It becomes:
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt >= '6/30/2014' AND WKORDER.wo_mod_dt <= '7/10/2014'
The current date is 6/30. The reserve word goes forward 0 days to find a beginning date of 6/30. It then goes forward 10 days to find an end date of 7/10.
'%TODAY%' - Inserts today's date.
![Closed](../../../../../Skins/Default/Stylesheets/Images/transparent.gif)
On Monday, 6/30/2014, a user runs the filter:
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt = '%TODAY%'
It becomes:
SELECT * FROM WKORDER WHERE WKORDER.wo_mod_dt = '6/30/2014'