Search code examples
sqloracle-databaseextract

EXTRACT function position in long query

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.

Solution

  • 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')