sql - 获取 Ora-00903：在 Oracle 中创建 CTE 日历时表名无效

Question

这是我的代码：

with calendar as
(
  select 
    to_date('1-jan-2019') + daynum as day_of_year, 
    Day_of_Week,
    rownum as Date_ID
  from 
    (
      select 
        rownum - 1 as daynum,
        to_char(to_date('1-jan-2019') + rownum - 1,'D') as Day_of_Week
      from 
        dual connect by rownum < sysdate - to_date('1-jan-2019') + 1000)
      where 
        day_of_week not in (1,7)
    ),

我需要知道为什么我会收到此错误以及如何解决它。当我在没有'With Calendar as'的情况下运行它时

select to_date('1-jan-2019') + daynum as day_of_year,
  Day_of_Week,rownum as Date_ID
  from 
  (
    select 
    rownum - 1 as daynum,
    to_char(to_date('1-jan-2019') + rownum - 1,'D') as Day_of_Week
    from dual connect by rownum < sysdate - to_date('1-jan-2019') + 1000)
    where day_of_week not in (1,7)

它运行，结果是

结果

后端故事：所以，我正在为我们的 SLA（服务水平协议）创建一个规则。在我们的仓库中，我们必须有一定的天数才能将订单送到码头。因此，通过生成日历并在下面添加“SLA”规则，我们可以获得一个“by_date”，显示我们需要在哪一天将它放在码头上。当订单下降时。

select distinct ord_num, max(sla) as sla from (
          select 
          h.ord_num,d10a.EDI_DATA_ID_VALUE as Order_TP, d10b.EDI_DATA_ID_VALUE as Augment,
            case when
            LOWER(d10b.EDI_DATA_ID_VALUE) LIKE '%shroud%' then 10
              when ord_lev1 in ('207347') then 4
              --Charles Cabinet
              when ord_lev1 in ('204611','204816','204819','205333','205818','205988') then 3
              --All Other cabinet IMNs or spare orders to cabinet
              when d10a.EDI_DATA_ID_VALUE = 'spare' then 0
              --SLA on spares, have to account for start date in another CTE
              else
              2
              end as sla --all other orders that arent charles, cabinet or a spare
      from e_ord_h h
      left join e_ord_d5 d5 on h.ord_num = d5.ord_num and d5.comp_code = 'S1'
      left join e_ord_D10 d10a on h.ord_num = d10a.ord_num and d10a.EDI_DATA_ID_DES = 'Capstan Order Type'and d10a.comp_code = 'S1'
      left join e_ord_D10 d10b on h.ord_num = d10b.ord_num and d10b.EDI_DATA_ID_DES = 'Capstan Augment'and d10b.comp_code = 'S1'
      where h.comp_code = 'S1' and flow_pros_code <> 'COOR' and ord_Stat = 'A'
      ) group by ord_num

标签： sqloracle

Answer 1

它对我有用。不过，您实际上并没有分享您的整个查询，所以我只是假设您分享的内容是最重要的。

数据库 12.2

with calendar as
(
  select 
    to_date('1-jan-2019') + daynum as day_of_year, 
    Day_of_Week,
    rownum as Date_ID
  from 
    (
      select 
        rownum - 1 as daynum,
        to_char(to_date('1-jan-2019') + rownum - 1,'D') as Day_of_Week
      from 
        dual connect by rownum < sysdate - to_date('1-jan-2019') + 1000)
      where 
        day_of_week not in (1,7)
    )
select * from calendar;