ORACLE SQL -向现有脚本插入日期范围

Question

我正在使用下面的脚本来获得每天的生产数字。有些日子没有生产数据，所以报告没有规定日期。

select distinct 
od.work_center_no, 
case  when od.work_center_no not in ('HPKG','SHRK') then so.revised_qty_due
      when od.work_center_no in ('HPKG','SHRK') then od.allowed_hours
end units_discs, 
case  when od.work_center_no not in ('HPKG','SHRK') then 'units'
      when od.work_center_no in ('HPKG','SHRK') then 'hrs'
end qty_hrs,
trunc(so.revised_due_date), so.plant, so.order_type
from gps_beweg gb, TABLE(leos_flatbom_pkg.GetFlatBOM(gb.part_no)) bom, 
leos_item li, shop_order so, operations_detail od
where gb.cunr = 'W30001'
and bom.av_part_no = li.av_part_no
and li.item_type in ('FP','MD')
and bom.av_part_no = so.part_no
and so.status_code between '2' and '8'
and so.order_no = od.order_no
and od.work_center_no not in ('AVDX','DPRN','FYIE','BVDB','ABDX','BBDS')
and od.work_center_no = 'HPKG'
and so.plant = 'W'
order by trunc(so.revised_due_date)

我想看看这些缺失的日期。我有下面的日期范围脚本。我想如果我运行这个脚本，我可以通过加入日期来加入我的数据。

select rownum - 1 + to_date(SYSDATE, 'dd/mm/yyyy') a
  from all_objects
  where rownum < to_date(SYSDATE+90, 'dd/mm/yyyy') -
    to_date(SYSDATE, 'dd/mm/yyyy') + 2

但是，我不知道如何将日期脚本添加到我现有的脚本中.如果它是一个子select (我确实尝试过，但我一直得到一个错误(ORA-00923: FROM关键字未在预期中找到)。

Answer 1

若要获取特定数量的记录，请使用

select level from dual
  connect by level < 91

将其与您的选择结合使用。

select distinct  od.work_center_no, 
  case
    when od.work_center_no not in ('HPKG','SHRK') then so.revised_qty_due
    when od.work_center_no in ('HPKG','SHRK') then od.allowed_hours
  end units_discs, 
  case
    when od.work_center_no not in ('HPKG','SHRK') then 'units'
    when od.work_center_no in ('HPKG','SHRK') then 'hrs'
  end qty_hrs,
  trunc(so.revised_due_date), so.plant, so.order_type
from gps_beweg gb,
     TABLE(leos_flatbom_pkg.GetFlatBOM(gb.part_no)) bom, 
     leos_item li,
     shop_order so,
     operations_detail od,
     (select trunc(sysdate) + level - 1 a_date from dual
        connect by level <= 90) dates
where trunc(so.revised_due_date(+)) = dates.a_date
  and nvl(gb.cunr,'W30001') = 'W30001'
  and bom.av_part_no = li.av_part_no(+)
  and nvl(li.item_type,'FP') in ('FP','MD')
  and bom.av_part_no(+) = so.part_no
  and nvl(so.status_code,'2') between '2' and '8'
  and so.order_no = od.order_no(+)
  and nvl(od.work_center_no not,'AVDX') in ('AVDX','DPRN','FYIE','BVDB','ABDX','BBDS')
  and nvl(od.work_center_no,'HPKG') = 'HPKG'
  and nvl(so.plant,'W') = 'W'
order by dates.a_date

需要外部联接和nvls来选择没有数据的日期。