EXTRACT函式在長查詢中的位置

我想請教以下方面的一些專業知識。我對 SQL 很陌生，所以請原諒任何錯誤或不正確的地方。我已經撰寫了以下 SQL，它可以根據我的需要處理現在的問題：

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

我現在需要從欄位中提取HOUR, MONTH, DAY, 。我僅使用基本查詢就設法使這個作業如下：DAY OF WEEKf.ORIGDTTM

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

我的問題是如何將EXTRACT函式添加到長而復雜的查詢中。我試圖將它放在許多地方但沒有結果。

uj5u.com熱心網友回復：

只需將它們添加到查詢中：

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

db<>在這里擺弄

uj5u.com熱心網友回復：

將它們添加到長查詢中，就像將它們添加到短查詢中一樣。

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

標籤：sql 甲骨文 提炼

上一篇：Django-嘗試最晚訂購get_object_or_404

下一篇：sql查詢到plsql程式