SELECT trim(XXXX.PMRNO) || ',' || trim(XXXX.BNO) || ',' || trim(XXXX.CNO) AS PMR,
XXXX.PMRBNO,
XXXX.TEAM,
XXXX."GROUP",
XXXX.PMRNO,
XXXX.REL,
XXXX.OWNER_NAME,
XXXX.CNO,
XXXX.BNO,
XXXX.SEVERITY,
XXXX.PRIORITY,
XXXX.APAR,
XXXX.PRODID,
XXXX.COUNTRY,
XXXX.BNO,
XXXX.CNO,
XXXX.CUSTNAME,
XXXX.CUSTNO,
XXXX.LASTAPPEND,
XXXX.FIRSTREVIEW,
XXXX.LASTREVIEW,
XXXX.DAYSOPEN,
XXXX.USERID,
XXXX.CURRENTQ,
XXXX.CONTACT,
XXXX.CATCODE,
XXXX.FIRSTQ,
XXXX.CATEGORY,
XXXX.SGDATE,
XXXX.SG,
XXXX.USERGROUP,
XXXX.FIRSTUSERID,
XXXX.FIRSTGROUP,
XXXX.FUP,
XXXX.FIRSTCTUSERID,
XXXX.FIRSTCTGROUP,
XXXX.FIRSTQTIME,
XXXX.CLOSEDDATE,
XXXX.CLOSEDDATE1,
XXXX.OPENDATE1,
XXXX.DEFQUES,
XXXX.SYSDOWN,
XXXX.CCMS,
XXXX.COMMENT,
XXXX.FIRSTUSERGROUP,
XXXX.TOUCHED,
XXXX."GROUP",
XXXX.OWNER_EMPLOYEENO,
XXXX.OWNER_NAME,
XXXX.FOLLOWUPINFO,
XXXX.SURVEYCODE,
XXXX.SUPPORTLEVEL,
XXXX.PREMIUMRESPONSE,
XXXX.PREVIOUSCENTER,
XXXX.PREVIOUSQUEUE,
XXXX.PREVIOUSPRIORITY,
XXXX.FIRSTQSEV,
XXXX.FIRSTQPRI,
XXXX.LASTCT,
XXXX.LOCALLASTCT,
XXXX.NEXT_QUEUE,
XXXX.RSTATUS,
XXXX.FIRSTPRI,
XXXX.FIRSTSEV,
XXXX.NEXTCT,
XXXX.EMAIL,
XXXX.RES,
XXXX.RESID,
XXXX.CRITSIT,
XXXX.SRID,
XXXX.IEEP,
XXXX.PMRBNO,
COMPIDS.COMPID,
COMPIDS.GROUP1,
COMPIDS.TEAM,
COMPIDS.LONGNAME,
teammember.NAME,
teammember.GEO,
teammember.USERID,
CALENDAR.DAY_NAME,
CALENDAR.DB2_DATE,
CALENDAR.MONTH_NAME,
CALENDAR.IBM_WEEK_ID,
CALENDAR.YEAR_ID,
L3OWNER.ACCEPT_DATE,
L3OWNER.BNO,
L3OWNER.CANCEL_DATE,
L3OWNER.CLOSED_DATE,
L3OWNER.CNO,
L3OWNER.CNUM,
L3OWNER.CREATE_DATE,
L3OWNER.EMAIL,
L3OWNER.L3GROUP,
L3OWNER.OWNER_TYPE,
L3OWNER.PMRNO,
L3OWNER.REJECT_DATE,
L3OWNER.REQUEST_DATE,
L3OWNER.STATUS,
L3OWNER.TRANSFER_DATE,


timestampdiff (8, char(

timestamp(XXXX.FIRSTCT)-

timestamp(XXXX.localOPENDATE))) AS L2_HOURS_TO_ACCEPT,

timestampdiff (8, char(

timestamp(L3OWNER.ACCEPT_DATE)-
timestamp(L3OWNER.REQUEST_DATE))) AS L3_HOURS_TO_ACCEPT,

timestampdiff (8, char(

timestamp(L3OWNER.CLOSED_DATE)-
timestamp(L3OWNER.ACCEPT_DATE))) AS L3_HOURS_TO_CLOSE

FROM ZZZ3.XXXX AS XXXX

LEFT JOIN YYY3.L3OWNER as L3OWNER ON L3OWNER.PMRNO = XXXX.PMRNO
AND L3OWNER.BNO = XXXX.BNO
AND L3OWNER.CNO = XXXX.CNO
AND L3OWNER.CREATE_DATE = XXXX.CREATE_DATE

LEFT JOIN ZZZ3.COMPIDS AS COMPIDS ON COMPIDS.COMPID = XXXX.PRODID AND COMPIDS.TEAM = XXXX.TEAM
LEFT JOIN ZZZ3.TEAMMEMBER AS teammember ON teammember.USERID = XXXX.USERID
LEFT JOIN ZZZ3.CALENDAR AS CALENDAR ON CALENDAR.DB2_DATE = XXXX.opendate1

WHERE XXXX.TEAM In ('ORA') AND (XXXX.OPENDATE1 >{d'2005-12-31'})

ORDER BY XXXX.PMRNO