Custom query Tickets not updated before last 3 months?
Dear SD colleagues,
I build a query via the wizard in "All Requests" as seen below. When trying to edit this query in the query window, I only get error message: "Java.lang.Exception: Only select queries can be executed". We use SD Plus 9.0 build 9045 with Microsoft SQL 2008 R2.
I would like to list tickets from predefined sites, technicians and groups which have been NOT updated for more than 3 months from now. "LAST_TECH_UPDATE" is longer than 3 months. How can I do that with the below query?
SELECT ti.FIRST_NAME 'Technician',scd.NAME 'Subcategory',aau.FIRST_NAME 'Requester',wo.TITLE 'Subject',wo.WORKORDERID 'Request ID',std.STATUSNAME 'Request Status',longtodate(wo.CREATEDTIME) 'Created Time',longtodate(wos.LAST_TECH_UPDATE) 'Last Update Time' FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID WHERE (((((std.ISPENDING = 1) AND ((((((((((((sdo.NAME LIKE N'%SITEA%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\') OR (sdo.NAME LIKE N'%SITEB%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\')) OR (sdo.NAME LIKE N'%SITEC%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (sdo.NAME LIKE N'%SITED%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (sdo.NAME LIKE N'%SITEE%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (sdo.NAME LIKE N'%SITEF%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (sdo.NAME LIKE N'%SITEG%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (sdo.NAME LIKE N'%SITEH%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\')) OR (sdo.NAME LIKE N'%SITEI%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (sdo.NAME LIKE N'%SITEJ%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\')) OR (sdo.NAME LIKE N'%SITEK%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (sdo.NAME LIKE N'%SITEL%' COLLATE SQL_Latin1_General_CP1_CI_AS))) AND (((((((qd.QUEUENAME LIKE N'%High Priority%' COLLATE SQL_Latin1_General_CP1_CI_AS) OR (qd.QUEUENAME LIKE N'%first%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\')) OR (qd.QUEUENAME LIKE N'%second%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\')) OR (qd.QUEUENAME LIKE N'%third%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\')) OR (qd.QUEUENAME LIKE N'%fourth%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\')) OR (qd.QUEUENAME LIKE N'%fifth%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\')) OR (qd.QUEUENAME IS NULL))) AND ((((((ti.FIRST_NAME LIKE N'%Tech1%' COLLATE SQL_Latin1_General_CP1_CI_AS) OR (ti.FIRST_NAME LIKE N'%Tech2%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (ti.FIRST_NAME LIKE N'%Tech3%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (ti.FIRST_NAME LIKE N'%Tech4%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (ti.FIRST_NAME LIKE N'%Tech5%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (ti.FIRST_NAME IS NULL))) AND (((longtodate(wos.LAST_TECH_UPDATE) >= datetolong('1388530800000') AND ((longtodate(wos.LAST_TECH_UPDATE) != 0) AND (longtodate(wos.LAST_TECH_UPDATE) IS NOT NULL))) AND ((longtodate(wos.LAST_TECH_UPDATE) <= datetolong('1438379999000') AND (((longtodate(wos.LAST_TECH_UPDATE) != 0) AND (longtodate(wos.LAST_TECH_UPDATE) IS NOT NULL)) AND (longtodate(wos.LAST_TECH_UPDATE) != -1))))) AND wo.ISPARENT='1' ORDER BY 1, 8, dpt.DEPTNAME, 3
Thanks for any help.
Regards,
Jan
I build a query via the wizard in "All Requests" as seen below. When trying to edit this query in the query window, I only get error message: "Java.lang.Exception: Only select queries can be executed". We use SD Plus 9.0 build 9045 with Microsoft SQL 2008 R2.
I would like to list tickets from predefined sites, technicians and groups which have been NOT updated for more than 3 months from now. "LAST_TECH_UPDATE" is longer than 3 months. How can I do that with the below query?
SELECT ti.FIRST_NAME 'Technician',scd.NAME 'Subcategory',aau.FIRST_NAME 'Requester',wo.TITLE 'Subject',wo.WORKORDERID 'Request ID',std.STATUSNAME 'Request Status',longtodate(wo.CREATEDTIME) 'Created Time',longtodate(wos.LAST_TECH_UPDATE) 'Last Update Time' FROM WorkOrder wo LEFT JOIN SDUser sdu ON wo.REQUESTERID=sdu.USERID LEFT JOIN AaaUser aau ON sdu.USERID=aau.USER_ID LEFT JOIN DepartmentDefinition dpt ON wo.DEPTID=dpt.DEPTID LEFT JOIN SiteDefinition siteDef ON wo.SITEID=siteDef.SITEID LEFT JOIN SDOrganization sdo ON siteDef.SITEID=sdo.ORG_ID LEFT JOIN WorkOrderStates wos ON wo.WORKORDERID=wos.WORKORDERID LEFT JOIN SubCategoryDefinition scd ON wos.SUBCATEGORYID=scd.SUBCATEGORYID LEFT JOIN SDUser td ON wos.OWNERID=td.USERID LEFT JOIN AaaUser ti ON td.USERID=ti.USER_ID LEFT JOIN StatusDefinition std ON wos.STATUSID=std.STATUSID LEFT JOIN WorkOrder_Queue woq ON wo.WORKORDERID=woq.WORKORDERID LEFT JOIN QueueDefinition qd ON woq.QUEUEID=qd.QUEUEID WHERE (((((std.ISPENDING = 1) AND ((((((((((((sdo.NAME LIKE N'%SITEA%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\') OR (sdo.NAME LIKE N'%SITEB%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\')) OR (sdo.NAME LIKE N'%SITEC%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (sdo.NAME LIKE N'%SITED%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (sdo.NAME LIKE N'%SITEE%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (sdo.NAME LIKE N'%SITEF%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (sdo.NAME LIKE N'%SITEG%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (sdo.NAME LIKE N'%SITEH%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\')) OR (sdo.NAME LIKE N'%SITEI%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (sdo.NAME LIKE N'%SITEJ%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\')) OR (sdo.NAME LIKE N'%SITEK%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (sdo.NAME LIKE N'%SITEL%' COLLATE SQL_Latin1_General_CP1_CI_AS))) AND (((((((qd.QUEUENAME LIKE N'%High Priority%' COLLATE SQL_Latin1_General_CP1_CI_AS) OR (qd.QUEUENAME LIKE N'%first%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\')) OR (qd.QUEUENAME LIKE N'%second%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\')) OR (qd.QUEUENAME LIKE N'%third%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\')) OR (qd.QUEUENAME LIKE N'%fourth%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\')) OR (qd.QUEUENAME LIKE N'%fifth%' COLLATE SQL_Latin1_General_CP1_CI_AS ESCAPE '\')) OR (qd.QUEUENAME IS NULL))) AND ((((((ti.FIRST_NAME LIKE N'%Tech1%' COLLATE SQL_Latin1_General_CP1_CI_AS) OR (ti.FIRST_NAME LIKE N'%Tech2%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (ti.FIRST_NAME LIKE N'%Tech3%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (ti.FIRST_NAME LIKE N'%Tech4%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (ti.FIRST_NAME LIKE N'%Tech5%' COLLATE SQL_Latin1_General_CP1_CI_AS)) OR (ti.FIRST_NAME IS NULL))) AND (((longtodate(wos.LAST_TECH_UPDATE) >= datetolong('1388530800000') AND ((longtodate(wos.LAST_TECH_UPDATE) != 0) AND (longtodate(wos.LAST_TECH_UPDATE) IS NOT NULL))) AND ((longtodate(wos.LAST_TECH_UPDATE) <= datetolong('1438379999000') AND (((longtodate(wos.LAST_TECH_UPDATE) != 0) AND (longtodate(wos.LAST_TECH_UPDATE) IS NOT NULL)) AND (longtodate(wos.LAST_TECH_UPDATE) != -1))))) AND wo.ISPARENT='1' ORDER BY 1, 8, dpt.DEPTNAME, 3
Thanks for any help.
Regards,
Jan