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.

The new filter displays all work orders with an end date that falls within the seven days previous to it being run.

Note: When using reserved words, enter them in all uppercase letters and enclose them in percent symbols (%)

The following is a list of all the reserved words available in Lucity Web.

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.

Requirements - An agency must own the 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.

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 Desktop application's Planning and Budgeting, Fiscal Year module.

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'

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'

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'

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.

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

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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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'

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'

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

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'

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'

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.

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.

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.

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.

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.

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'