Hi Team,
I have what I hope is a simple question.
I have the below query written:
SELECT
main.*,
TO_CHAR(main.HH, 'FM900') || ':' || TO_CHAR(main.MM, 'FM00') AS HH_MM
FROM
(SELECT
main.OTGTM,
FLOOR(main.OTGTM / 60) AS HH,
MOD(main.OTGTM, 60) AS MM
FROM
ACTV.HEADER main) main
What I am trying to do is incorporate this into my main code below. I have tried placing it in various different places and looked at related topics but keep getting various error messages and I am completely lost. I'm sure this must be simple so I would like to ask for some help please
SELECT
to_char(main.CREATEDTTM,'DD/MM/YYYY') AS "Creation Date",
main.SITENAME AS "Site Name",
' ' AS "Site Type",
' ' AS "Severity",
main.TICKET AS "Ticket Number",
CASE
WHEN main.RECEIVEDVIA = 'ALARM' THEN 'PROACTIVE'
ELSE 'REACTIVE' END AS "PROACTIVE/REACTIVE",
CASE
WHEN main.SUMMARY LIKE '%DeviceUpDown%' THEN 'DeviceUpDown'
WHEN main.SUMMARY LIKE '%SDWANPathUpDown%' THEN 'SDWANPathUpDown'
WHEN main.SUMMARY LIKE '%WirelessControl%' THEN 'WirelessController
ELSE 'REACTIVE OR MAINT' END AS INCTYPE,
main.PRTY AS "Priority",
main.ESC AS "Escalation Level",
main.TKTID,
main.STA || '/' || main.STS,
to_char(main.CREATEDTTM, 'DD-MM-YYYY hh:mm:ss') AS "Incident Opened Date/Time-GMT",
main.OTGTM AS "Outage Duration (hh:mm:ss)"
FROM ACTV.HEADER main
WHERE main.CUSNAME LIKE 'ACME%'
AND main.ITIL_TYPE = 'I'
AND main.CREATEDTTM >= to_timestamp('2023-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS');
Thank you in advance
