EXTRACT function position in long query-CodePudding

I would like to ask for some expertise on the below please. I am quite new to SQL, so please forgive any mistakes or incorrectness. I have written the below SQL which works as I need with now issues:

SELECT a.TICKET,
       f.CODE,
       f.SEQUENCE,
       f.CLEARSEQUENCE,
       f.TOTALMINUTES,
       a.SEV,
       a.ORIGSEV,
       a.CUSTOMER,
       a.WORKGROUP,
       a.NOC,
       d.COUNTRYA,
       d.COUNTRYZ,
       a.IDENTIFIER,
       f.ORIGDTTM,
FROM   SYSTEMACTV.T3SEVHEADER a
       LEFT OUTER JOIN SYSTEMACTV.T3SEVCUSTOMER d ON a.TICKET=d.TICKET 
       LEFT OUTER JOIN SYSTEMACTV.T3SEVCODEDATA f ON a.TICKET=f.TICKET
WHERE  a.CLOSEDDT >= to_timestamp('2021-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
AND    a.WORKGROUP IN ('SVCDSK.DES-INTL')
AND    a.SEV in (1,2,3,4)
AND    d.COUNTRYA IN ('CHN','JPN','HKG','GUM','IDN','PRK','KOR','MYS','MMR','NZL','PHL','SGP','TWN','THA','VNM')

I now need to extract HOUR, MONTH, DAY, DAY OF WEEK from the f.ORIGDTTM field. I have managed to get this working as below using just the basic query:

SELECT EXTRACT(hour FROM ORIGDTTM) "HOUR",
       EXTRACT(month FROM ORIGDTTM) "MONTH"
FROM   ETMSACTV.T3TKTNADDATA;

My problem is how can I add the EXTRACT functions into the long, complex query. I have tried to place it within a number of places with no results.

CodePudding user response：

Add them to a long query, just as you'd add them to a short one.

SELECT a.ticket,
       f.code,
       f.sequence,
       f.clearsequence,
       f.totalminutes,
       a.sev,
       a.origsev,
       a.customer,
       a.workgroup,
       a.noc,
       d.countrya,
       d.countryz,
       a.identifier,
       f.origdttm,
       --
       EXTRACT (HOUR FROM f.origdttm) "HOUR",         --> here
       EXTRACT (MONTH FROM f.origdttm) "MONTH"        --> here
  FROM systemactv.t3sevheader a
       LEFT OUTER JOIN systemactv.t3sevcustomer d ON a.ticket = d.ticket
       LEFT OUTER JOIN systemactv.t3sevcodedata f ON a.ticket = f.ticket
 WHERE     a.closeddt >=
           TO_TIMESTAMP ('2021-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
       AND a.workgroup IN ('SVCDSK.DES-INTL')
       AND a.sev IN (1,
                     2,
                     3,
                     4)
       AND d.countrya IN ('CHN',
                          'JPN',
                          'HKG',
                          'GUM',
                          'IDN',
                          'PRK',
                          'KOR',
                          'MYS',
                          'MMR',
                          'NZL',
                          'PHL',
                          'SGP',
                          'TWN',
                          'THA',
                          'VNM')

CodePudding user response：

Just add them to the query:

SELECT a.TICKET,
       f.CODE,
       f.SEQUENCE,
       f.CLEARSEQUENCE,
       f.TOTALMINUTES,
       a.SEV,
       a.ORIGSEV,
       a.CUSTOMER,
       a.WORKGROUP,
       a.NOC,
       d.COUNTRYA,
       d.COUNTRYZ,
       a.IDENTIFIER,
       f.ORIGDTTM,
       EXTRACT(MONTH FROM f.ORIGDTTM) AS month,
       EXTRACT(DAY   FROM f.ORIGDTTM) AS day,
       EXTRACT(HOUR  FROM f.ORIGDTTM) AS hour,
       TRUNC(f.ORIGDTTM) - TRUNC(f.ORIGDTTM, 'IW') AS day_of_week,
         -- monday = 0, ..., sunday = 6
       TO_CHAR(f.ORIGDTTM, 'DY', 'NLS_DATE_LANGUAGE=English') AS day_of_week_alt
FROM   SYSTEMACTV.T3SEVHEADER a
       LEFT OUTER JOIN SYSTEMACTV.T3SEVCUSTOMER d ON a.TICKET=d.TICKET 
       LEFT OUTER JOIN SYSTEMACTV.T3SEVCODEDATA f ON a.TICKET=f.TICKET
WHERE  a.CLOSEDDT >= TIMESTAMP '2021-01-01 00:00:00'
AND    a.WORKGROUP IN ('SVCDSK.DES-INTL')
AND    a.SEV in (1,2,3,4)
AND    d.COUNTRYA IN ('CHN','JPN','HKG','GUM','IDN','PRK','KOR','MYS','MMR','NZL','PHL','SGP','TWN','THA','VNM')