Using NEXT_DAY( trunc(date)) in open script

Stry

Hi All,

I have developed a report query in Toad that works. I want to create a maximo report based on this.
however when I create a maximo report in Eclipse the dataset is empty.
The report will be run from maximo.
Here is the open script:
maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());
maximoDataSet.open();
var sqlText = new String();
// Add MM-Maximo query to sqlText variable.
sqlText = " SELECT WORKORDER.CNR_AREA, "
+ " WORKORDER.WONUM, "
+ " WORKORDER.DESCRIPTION, "
+ " LONGDESCRIPTION.LDTEXT LONG_DESC, "
+ " WORKORDER.LOCATION, "
+ " TRUNC(WORKORDER.ACTSTART) ACTSTART, "
+ " TRUNC(WORKORDER.ACTFINISH) ACTFINISH, "
+ " WORKORDER.ESTDUR, "
+ " WORKORDER.LEAD, "
+ " WORKORDER.WOPRIORITY, "
+ " WORKORDER.STATUS "
+ " FROM WORKORDER , LONGDESCRIPTION "
+ " WHERE " + params["where"]
+ " AND WORKORDER.WORKORDERID = LONGDESCRIPTION.LDKEY(+) "
+ " AND LONGDESCRIPTION.LDOWNERCOL = 'DESCRIPTION' "
+ " AND LONGDESCRIPTION.LDOWNERTABLE = 'WORKORDER' "
+ " AND WOCLASS = 'WORKORDER' AND WOPRIORITY IN (6, 7) "
+ " AND WORKORDER.ACTSTART BETWEEN NEXT_DAY(TRUNC(SYSDATE - 14), 'monday') AND NEXT_DAY(TRUNC(SYSDATE - 7), 'sunday') "
+ " ORDER BY WORKORDER.CNR_AREA ASC";
maximoDataSet.setQuery(sqlText);

If I comment out the ACTSTART BETWEEN line the report works and data is returned. as soon as I add that line back, datset returns empty. the idea is I want start dates that fall in the privous week from the sysdate.
Can I not use the NEXT_DAY function, if not what are my opions?

Thanks for the help

micajblock

Try 'MONDAY' and 'SUNDAY'.