sql - 数据不会返回

Question

我在下面的 SQL 中添加了一个时间戳语句，但它没有将它添加到表中。

with cx(seq, user_id, mod_date_time, menu_optn_name) as (
    select 1, 'U1', timestamp '2007-05-21 16:00:00', 'O1'  from dual),
 cc(seq, user_id, mod_date_time, menu_optn_name) as (
   select seq, user_id, cast(mod_date_time as date), menu_optn_name from cx)

select
  user_id, MENU_OPTN_NAME, MOD_DATE_TIME,
  case
    when MOD_DATE_TIME > prior MOD_DATE_TIME+(1/96) and user_id = prior user_id then round((MOD_DATE_TIME - prior MOD_DATE_TIME)*1440,2)
    else null
  end as TIME_GAP
from
  (select
     ptt.user_id, MENU_OPTN_NAME, ptt.MOD_DATE_TIME,
     row_number() over (partition by ptt.user_id order by ptt.MOD_DATE_TIME) seq
   from PROD_TRKG_TRAN ptt
     join cd_master cm on
       ptt.cd_master_id = cm.cd_master_id
   Where
     MENU_OPTN_NAME = 'Cycle Cnt {Reserve}' --CHANGE BASED ON WHAT YOUR TRACKING... MENU NAMES AT THE BOTTOM
     and ptt.user_id = 'SCOUNCIL' --CHANGE BASED ON WHO YOU WANT TO TRACK FOR A GAP...
     and cm.cd_master_id =
       (select cd_master_id
        from cd_master
        where
          co = '&CO'
          and div = '&DIV')
     and ptt.create_date_time >=
/*Today*/ trunc(sysdate)
--/*This Week*/              trunc(sysdate-(to_char(sysdate,'D')-1))
     --/*This Month*/            trunc(sysdate)-(to_char(sysdate,'DD')-1)
     --/*Date Range*/            '&FromDate' and ptt.create_date_time-1 < '&ToDate'
     --group by ptt.user_id
)cc
CONNECT BY
  user_id = prior user_id
  and seq = prior seq+1
start with
  seq = 1

我有查询向我显示任何时间间隔大于 15 的东西会告诉我间隔有多长，但我希望它考虑班次的开始时间是下午 4 点，所以如果他们在下午 4 点开始工作并且不做系统中的任何内容直到下午 4:41，然后有 41 分钟的间隙。不知道我做错了什么...我单独构建了时间戳部分，然后将其放入查询中，这可能并不意味着与查询一起使用，应该单独使用。感谢您对此问题的任何见解。

标签： sqloracle