请教一个ORA错误，关于有效日期格式的。

hfwang

ORA代码忘记了……
运行一个很长的SCRIPT，提示查询出的日期格式无效，类似20131032之类的。
它是查SYSDATE到SYSDATE-7的数据，如果我改成查SYSDATE-4到（SYSDATE-4）-7的数据，没错。
但是SYSDATE-3到（SYSDATE-3）-7的数据，就开始错了。
而这个SYSDATE查的是文件名字，也就是说9月29日开始的文件名字有日期格式无效，类似EVENT20131901这类。

请看下面SCRIPT中红色的，是不是我应该去查那些有这文件名的表查处奇怪的名字，类似表INCOMING_FILE，SCHEDULE_HISTORY， SCHEDULE_LOG ？

请指教

hfwang

SELECT F_Array.fieldstostring(arrayvarchar2 (
           FILE_TYPE_DESC,EXPECTEDTM,DAY1,ST1,DAY2,ST2,DAY3,ST3,DAY4,ST4,DAY5,ST5,SATURDAY,STSAT,SUNDAY,STSUN
       ),CHR(124) )
from (
select f.file_type_desc
      --,substr(f.file_type_desc,1,instr(f.file_type_desc,'-'))
tmp1,substr(f.file_type_desc,instr(f.file_type_desc,'-')+1,1) tmp2
      ,decode(replace(f.file_type_desc,benchmark.file_type_desc||'-'),'begin',b_Day1,'end',e_Day1,'duration',duration)
expectedTm
      ,case when Day1 = '00:00' then null else replace(Day1,'19991231
00:00') end Day1
      ,case when replace(replace(Day1,'19991231 00:00'),'00:00') <=
decode(replace(f.file_type_desc,benchmark.file_type_desc||'-'),'begin',b_Day1,'end',e_Day1,'duration',duration)
then 'G' else 'Y' end st1
      ,case when Day2 = '00:00' then null else replace(Day2,'19991231
00:00') end Day2
      ,case when replace(replace(Day2,'19991231 00:00'),'00:00') <=
decode(replace(f.file_type_desc,benchmark.file_type_desc||'-'),'begin',b_Day2,'end',e_Day2,'duration',duration)
then 'G' else 'Y' end st2
      ,case when Day3 = '00:00' then null else replace(Day3,'19991231
00:00') end Day3
      ,case when replace(replace(Day3,'19991231 00:00'),'00:00') <=
decode(replace(f.file_type_desc,benchmark.file_type_desc||'-'),'begin',b_Day3,'end',e_Day3,'duration',duration)
then 'G' else 'Y' end st3
      ,case when Day4 = '00:00' then null else replace(Day4,'19991231
00:00') end Day4
      ,case when replace(replace(Day4,'19991231 00:00'),'00:00') <=
decode(replace(f.file_type_desc,benchmark.file_type_desc||'-'),'begin',b_Day4,'end',e_Day4,'duration',duration)
then 'G' else 'Y' end st4
      ,case when Day5 = '00:00' then null else replace(Day5,'19991231
00:00') end Day5
      ,case when replace(replace(Day5,'19991231 00:00'),'00:00') <=
decode(replace(f.file_type_desc,benchmark.file_type_desc||'-'),'begin',b_Day5,'end',e_Day5,'duration',duration)
then 'G' else 'Y' end st5
      ,case when Saturday = '00:00' then null else
replace(Saturday,'19991231 00:00') end Saturday
      ,case when replace(replace(Saturday,'19991231 00:00'),'00:00')
<= decode(substr(f.file_type_desc,instr(f.file_type_desc,'-')+1,1),'b',b_Saturday,'e',e_Saturday,'t',duration)
then 'G' else 'Y' end stSat
      ,case when Sunday = '00:00' then null else
replace(Sunday,'19991231 00:00') end Sunday
      ,case when replace(replace(Sunday,'19991231 00:00'),'00:00')  <=
decode(substr(f.file_type_desc,instr(f.file_type_desc,'-')+1,1),'b',b_Sunday,'e',e_Sunday,'t',duration)
then 'G' else 'Y' end stSun
      --,benchmark.*,
from (
SELECT
       (file_type_desc)||'-begin' file_type_desc,
       TO_CHAR(MAX(CASE WHEN file_dt = report_dt-7 THEN LOADED_DT ELSE
TO_DATE('19991231','YYYYMMDD') END), 'YYYYMMDD HH24:MI') Day1,
       TO_CHAR(MAX(CASE WHEN file_dt = report_dt-6 THEN LOADED_DT ELSE
TO_DATE('19991231','YYYYMMDD') END), 'YYYYMMDD HH24:MI') Day2,
       TO_CHAR(MAX(CASE WHEN file_dt = report_dt-5 THEN LOADED_DT ELSE
TO_DATE('19991231','YYYYMMDD') END), 'YYYYMMDD HH24:MI') Day3,
       TO_CHAR(MAX(CASE WHEN file_dt = report_dt-2 THEN LOADED_DT ELSE
TO_DATE('19991231','YYYYMMDD') END), 'YYYYMMDD HH24:MI') Day4,
       TO_CHAR(MAX(CASE WHEN file_dt = report_dt-1 THEN LOADED_DT ELSE
TO_DATE('19991231','YYYYMMDD') END), 'YYYYMMDD HH24:MI') Day5,
       TO_CHAR(MAX(CASE WHEN file_dt = report_dt-4 THEN LOADED_DT ELSE
TO_DATE('19991231','YYYYMMDD') END), 'YYYYMMDD HH24:MI') Saturday,
       TO_CHAR(MAX(CASE WHEN file_dt = report_dt-3 THEN LOADED_DT ELSE
TO_DATE('19991231','YYYYMMDD') END), 'YYYYMMDD HH24:MI') Sunday, 2 ord
FROM (
SELECT report_dt,file_name
file_name,INSTR(file_name,to_char(sysdate,'yyyy')) inst
,NVL(SUBSTR(REPLACE(file_name,'EVENT_'),1,INSTR(file_name,'_'||to_char(sysdate,'yyyy'))-7),SUBSTR(file_name,1,4))
file_type
,TO_DATE(SUBSTR(file_name,INSTR(file_name,to_char(sysdate,'yyyy')),8),'YYYYMMDD')+1
file_dt
,report_dt-7 curr_dt
,'File '||(CASE
       WHEN FILE_NAME LIKE 'CUST%' OR
SUBSTR(REPLACE(file_name,'EVENT_'),1,INSTR(file_name,'_'||to_char(sysdate,'yyyy'))-7)
IN ('100','12000')
               THEN'CUSTOMER'
       WHEN SUBSTR(REPLACE(file_name,'EVENT_'),1,INSTR(file_name,'_'||to_char(sysdate,'yyyy'))-7)
IN ('401','402','403','404','405','406')
               THEN 'EVENTS 401to406'
       WHEN NVL(SUBSTR(REPLACE(file_name,'EVENT_'),1,INSTR(file_name,'_'||to_char(sysdate,'yyyy'))-7),SUBSTR(file_name,1,4))
IN ('LIST')
               THEN 'LIST'
       ELSE 'EVENTS
'||SUBSTR(REPLACE(file_name,'EVENT_'),1,INSTR(file_name,'_'||to_char(sysdate,'yyyy'))-7)
END) file_type_desc
,LOADED_DT, FIRST_PROCESSED_DT
FROM INCOMING_FILE, (SELECT NEXT_DAY(TRUNC(SYSDATE)-4, 'Wednesday')
report_dt FROM Dual) b
WHERE
--KHOI ADEDD
SUBSTR(file_name,INSTR(file_name,to_char(sysdate,'yyyy')),8) BETWEEN
TO_CHAR(report_dt-8,'YYYYMMDD') AND TO_CHAR(report_dt-1,'YYYYMMDD')
--received_dt > sysdate - 1
--KHOI ADDED
AND LOADED_DT IS NOT NULL
UNION
SELECT report_dt,NAME file_name,INSTR(NAME,'day') inst
      ,REPLACE(NAME,' Schedule') file_type
      ,TRUNC(run_dt) file_dt
      ,report_dt-6 curr_dt
      ,'Schedule
'||ltrim(replace(DECODE(GREATEST(INSTR(NAME,'day'),0),0,NAME,NVL(REPLACE(SUBSTR(NAME,(INSTR(NAME,'day')+3)),'-
'),NAME)),'Week Day - ')) file_type_desc
      ,DECODE(schl.SCHL_ID,schp.SCHL_ID,schs.LOG_DT,schl.LOG_DT) LOADED_DT
      ,schp.LOG_DT PROCESSED_DT
FROM   SCHEDULE_HISTORY schh,SCHEDULE_LOG schp, SCHEDULE_LOG schs,
SCHEDULE_LOG schl, (SELECT NEXT_DAY(TRUNC(SYSDATE)-4, 'Wednesday')
report_dt FROM Dual) b
WHERE  schh.schh_id = schs.schh_fk AND  schs.schh_fk=schp.schh_fk AND
schp.schh_fk = schl.schh_fk
AND    TO_CHAR(run_dt,'YYYYMMDD') BETWEEN
TO_CHAR(report_dt-7,'YYYYMMDD') AND TO_CHAR(report_dt-1,'YYYYMMDD')
AND   SUBSTR(schs.description,1,17) IN ('Starting schedule')
AND   schp.description IN ('Schedule ended.')
AND   schl.description IN ('Schedule pre-requisites have been
met.','Schedule ended.')
AND   schl.SCHL_ID = (SELECT MIN(SCHL_ID) FROM SCHEDULE_LOG schl2
WHERE schp.schh_fk = schl2.schh_fk AND schl2.description IN ('Schedule
pre-requisites have been met.','Schedule ended.'))
) t
GROUP BY file_type_desc
UNION
SELECT
       (file_type_desc)||'-end' file_type_desc,
       TO_CHAR(MAX(CASE WHEN file_dt = report_dt-7 THEN
NVL(FIRST_PROCESSED_DT,LOADED_DT) ELSE TO_DATE('19991231','YYYYMMDD')
END), 'YYYYMMDD HH24:MI') Day1,
       TO_CHAR(MAX(CASE WHEN file_dt = report_dt-6 THEN
NVL(FIRST_PROCESSED_DT,LOADED_DT) ELSE TO_DATE('19991231','YYYYMMDD')
END), 'YYYYMMDD HH24:MI') Day2,
       TO_CHAR(MAX(CASE WHEN file_dt = report_dt-5 THEN
NVL(FIRST_PROCESSED_DT,LOADED_DT) ELSE TO_DATE('19991231','YYYYMMDD')
END), 'YYYYMMDD HH24:MI') Day3,
       TO_CHAR(MAX(CASE WHEN file_dt = report_dt-2 THEN
NVL(FIRST_PROCESSED_DT,LOADED_DT) ELSE TO_DATE('19991231','YYYYMMDD')
END), 'YYYYMMDD HH24:MI') Day4,
       TO_CHAR(MAX(CASE WHEN file_dt = report_dt-1 THEN
NVL(FIRST_PROCESSED_DT,LOADED_DT) ELSE TO_DATE('19991231','YYYYMMDD')
END), 'YYYYMMDD HH24:MI') Day5,
       TO_CHAR(MAX(CASE WHEN file_dt = report_dt-4 THEN
NVL(FIRST_PROCESSED_DT,LOADED_DT) ELSE TO_DATE('19991231','YYYYMMDD')
END), 'YYYYMMDD HH24:MI') Saturday,
       TO_CHAR(MAX(CASE WHEN file_dt = report_dt-3 THEN
NVL(FIRST_PROCESSED_DT,LOADED_DT) ELSE TO_DATE('19991231','YYYYMMDD')
END), 'YYYYMMDD HH24:MI') Sunday, 3 ord
FROM (
SELECT report_dt,file_name
file_name,INSTR(file_name,to_char(sysdate,'yyyy')) inst
,NVL(SUBSTR(REPLACE(file_name,'EVENT_'),1,INSTR(file_name,'_'||to_char(sysdate,'yyyy'))-7),SUBSTR(file_name,1,4))
file_type
,TO_DATE(SUBSTR(file_name,INSTR(file_name,to_char(sysdate,'yyyy')),8),'YYYYMMDD')+1
file_dt
,report_dt-7 curr_dt
,'File '||(CASE
       WHEN FILE_NAME LIKE 'CUST%' OR
SUBSTR(REPLACE(file_name,'EVENT_'),1,INSTR(file_name,'_'||to_char(sysdate,'yyyy'))-7)
IN ('100','12000')
               THEN'CUSTOMER'
       WHEN SUBSTR(REPLACE(file_name,'EVENT_'),1,INSTR(file_name,'_'||to_char(sysdate,'yyyy'))-7)
IN ('401','402','403','404','405','406')
               THEN'EVENTS 401to406'
       WHEN NVL(SUBSTR(REPLACE(file_name,'EVENT_'),1,INSTR(file_name,'_'||to_char(sysdate,'yyyy'))-7),SUBSTR(file_name,1,4))
IN ('LIST')
               THEN 'LIST'
       ELSE 'EVENTS
'||SUBSTR(REPLACE(file_name,'EVENT_'),1,INSTR(file_name,'_'||to_char(sysdate,'yyyy'))-7)
END) file_type_desc
,LOADED_DT, FIRST_PROCESSED_DT
FROM INCOMING_FILE, (SELECT NEXT_DAY(TRUNC(SYSDATE)-4, 'Wednesday')
report_dt FROM Dual) b
WHERE
--Khoi Added
SUBSTR(file_name,INSTR(file_name,to_char(sysdate,'yyyy')),8) BETWEEN
TO_CHAR(report_dt-8,'YYYYMMDD') AND TO_CHAR(report_dt-1,'YYYYMMDD')
--received_dt > sysdate - 1
--Khoi Added
AND LOADED_DT IS NOT NULL
UNION
SELECT report_dt,NAME file_name,INSTR(NAME,'day') inst
      ,REPLACE(NAME,' Schedule') file_type
      ,TRUNC(run_dt) file_dt
      ,report_dt-6 curr_dt
      ,'Schedule
'||ltrim(replace(DECODE(GREATEST(INSTR(NAME,'day'),0),0,NAME,NVL(REPLACE(SUBSTR(NAME,(INSTR(NAME,'day')+3)),'-
'),NAME)),'Week Day - ')) file_type_desc
      ,DECODE(schl.SCHL_ID,schp.SCHL_ID,schs.LOG_DT,schl.LOG_DT) LOADED_DT
      ,schp.LOG_DT PROCESSED_DT
FROM   SCHEDULE_HISTORY schh,SCHEDULE_LOG schp, SCHEDULE_LOG schs,
SCHEDULE_LOG schl, (SELECT NEXT_DAY(TRUNC(SYSDATE)-4, 'Wednesday')
report_dt FROM Dual) b
WHERE  schh.schh_id = schs.schh_fk AND  schs.schh_fk=schp.schh_fk AND
schp.schh_fk = schl.schh_fk
AND    TO_CHAR(run_dt,'YYYYMMDD') BETWEEN
TO_CHAR(report_dt-7,'YYYYMMDD') AND TO_CHAR(report_dt-1,'YYYYMMDD')
AND   SUBSTR(schs.description,1,17) IN ('Starting schedule')
AND   schp.description IN ('Schedule ended.')
AND   schl.description IN ('Schedule pre-requisites have been
met.','Schedule ended.')
AND   schl.SCHL_ID = (SELECT MIN(SCHL_ID) FROM SCHEDULE_LOG schl2
WHERE schp.schh_fk = schl2.schh_fk AND schl2.description IN ('Schedule
pre-requisites have been met.','Schedule ended.'))
) t
GROUP BY file_type_desc
UNION
SELECT
       (file_type_desc)||'-duration' file_type_desc,
       TO_CHAR(TRUNC(SYSDATE)+(MAX(CASE WHEN file_dt = report_dt-7
THEN FIRST_PROCESSED_DT ELSE TO_DATE('19991231','YYYYMMDD') END)
                                -MAX(CASE WHEN file_dt = report_dt-7
THEN LOADED_DT ELSE TO_DATE('19991231','YYYYMMDD') END) ), 'HH24:MI')
Day1,
       TO_CHAR(TRUNC(SYSDATE)+(MAX(CASE WHEN file_dt = report_dt-6
THEN FIRST_PROCESSED_DT ELSE TO_DATE('19991231','YYYYMMDD') END)
                                -MAX(CASE WHEN file_dt = report_dt-6
THEN LOADED_DT ELSE TO_DATE('19991231','YYYYMMDD') END) ), 'HH24:MI')
Day2,
       TO_CHAR(TRUNC(SYSDATE)+(MAX(CASE WHEN file_dt = report_dt-5
THEN FIRST_PROCESSED_DT ELSE TO_DATE('19991231','YYYYMMDD') END)
                                -MAX(CASE WHEN file_dt = report_dt-5
THEN LOADED_DT ELSE TO_DATE('19991231','YYYYMMDD') END) ), 'HH24:MI')
Day3,
       TO_CHAR(TRUNC(SYSDATE)+(MAX(CASE WHEN file_dt = report_dt-2
THEN FIRST_PROCESSED_DT ELSE TO_DATE('19991231','YYYYMMDD') END)
                                -MAX(CASE WHEN file_dt = report_dt-2
THEN LOADED_DT ELSE TO_DATE('19991231','YYYYMMDD') END) ), 'HH24:MI')
Day4,
       TO_CHAR(TRUNC(SYSDATE)+(MAX(CASE WHEN file_dt = report_dt-1
THEN FIRST_PROCESSED_DT ELSE TO_DATE('19991231','YYYYMMDD') END)
                                -MAX(CASE WHEN file_dt = report_dt-1
THEN LOADED_DT ELSE TO_DATE('19991231','YYYYMMDD') END) ), 'HH24:MI')
Day5,
       TO_CHAR(TRUNC(SYSDATE)+(MAX(CASE WHEN file_dt = report_dt-4
THEN FIRST_PROCESSED_DT ELSE TO_DATE('19991231','YYYYMMDD') END)
                                -MAX(CASE WHEN file_dt = report_dt-4
THEN LOADED_DT ELSE TO_DATE('19991231','YYYYMMDD') END) ), 'HH24:MI')
Saturday,
       TO_CHAR(TRUNC(SYSDATE)+(MAX(CASE WHEN file_dt = report_dt-3
THEN FIRST_PROCESSED_DT ELSE TO_DATE('19991231','YYYYMMDD') END)
                                -MAX(CASE WHEN file_dt = report_dt-3
THEN LOADED_DT ELSE TO_DATE('19991231','YYYYMMDD') END) ), 'HH24:MI')
Sunday, 4 ord
FROM (
SELECT report_dt,file_name
file_name,INSTR(file_name,to_char(sysdate,'yyyy')) inst
,NVL(SUBSTR(REPLACE(file_name,'EVENT_'),1,INSTR(file_name,'_'||to_char(sysdate,'yyyy'))-7),SUBSTR(file_name,1,4))
file_type
,TO_DATE(SUBSTR(file_name,INSTR(file_name,to_char(sysdate,'yyyy')),8),'YYYYMMDD')+1
file_dt
,report_dt-7 curr_dt
,'File '||(CASE
       WHEN FILE_NAME LIKE 'CUST%' OR
SUBSTR(REPLACE(file_name,'EVENT_'),1,INSTR(file_name,'_'||to_char(sysdate,'yyyy'))-7)
IN ('100','12000')
               THEN 'CUSTOMER'
       WHEN SUBSTR(REPLACE(file_name,'EVENT_'),1,INSTR(file_name,'_'||to_char(sysdate,'yyyy'))-7)
IN ('401','402','403','404','405','406')
               THEN 'EVENTS 401to406'
       WHEN NVL(SUBSTR(REPLACE(file_name,'EVENT_'),1,INSTR(file_name,'_'||to_char(sysdate,'yyyy'))-7),SUBSTR(file_name,1,4))
IN ('LIST')
               THEN 'LIST'
       ELSE 'EVENTS
'||SUBSTR(REPLACE(file_name,'EVENT_'),1,INSTR(file_name,'_'||to_char(sysdate,'yyyy'))-7)
END) file_type_desc
,LOADED_DT, FIRST_PROCESSED_DT
FROM INCOMING_FILE, (SELECT NEXT_DAY(TRUNC(SYSDATE)-4, 'Wednesday')
report_dt FROM Dual) b
WHERE
--Khoi Added
SUBSTR(file_name,INSTR(file_name,to_char(sysdate,'yyyy')),8) BETWEEN
TO_CHAR(report_dt-8,'YYYYMMDD') AND TO_CHAR(report_dt-1,'YYYYMMDD')
--received_dt > sysdate - 1
--Khoi Added
AND LOADED_DT IS NOT NULL
UNION
SELECT report_dt,NAME file_name,INSTR(NAME,'day') inst
      ,REPLACE(NAME,' Schedule') file_type
      ,TRUNC(run_dt) file_dt
      ,report_dt-6 curr_dt
      ,'Schedule
'||ltrim(replace(DECODE(GREATEST(INSTR(NAME,'day'),0),0,NAME,NVL(REPLACE(SUBSTR(NAME,(INSTR(NAME,'day')+3)),'-
'),NAME)),'Week Day - ')) file_type_desc
      ,DECODE(schl.SCHL_ID,schp.SCHL_ID,schs.LOG_DT,schl.LOG_DT) LOADED_DT
      ,schp.LOG_DT PROCESSED_DT
FROM   SCHEDULE_HISTORY schh,SCHEDULE_LOG schp, SCHEDULE_LOG schs,
SCHEDULE_LOG schl, (SELECT NEXT_DAY(TRUNC(SYSDATE)-4, 'Wednesday')
report_dt FROM Dual) b
WHERE  schh.schh_id = schs.schh_fk AND  schs.schh_fk=schp.schh_fk AND
schp.schh_fk = schl.schh_fk
AND    TO_CHAR(run_dt,'YYYYMMDD') BETWEEN
TO_CHAR(report_dt-7,'YYYYMMDD') AND TO_CHAR(report_dt-1,'YYYYMMDD')
AND   SUBSTR(schs.description,1,17) IN ('Starting schedule')
AND   schp.description IN ('Schedule ended.')
AND   schl.description IN ('Schedule pre-requisites have been
met.','Schedule ended.')
AND   schl.SCHL_ID = (SELECT MIN(SCHL_ID) FROM SCHEDULE_LOG schl2
WHERE schp.schh_fk = schl2.schh_fk AND schl2.description IN ('Schedule
pre-requisites have been met.','Schedule ended.'))
) t
GROUP BY file_type_desc
UNION
select file_type_desc||'-benchmark' file_type_desc
       ,to_char(day1+begin_add,'YYYYMMDD HH24:MI') b_Day1,
to_char(day2+begin_add,'YYYYMMDD HH24:MI') b_Day2,
to_char(day3+begin_add,'YYYYMMDD HH24:MI') b_Day3
       ,to_char(day4+begin_add,'YYYYMMDD HH24:MI') b_Day4,
to_char(day5+begin_add,'YYYYMMDD HH24:MI') b_Day5
       ,to_char(SaturDay+begin_add+sat_add,'YYYYMMDD HH24:MI')
b_Saturday, to_char(SunDay+begin_add+sun_add,'YYYYMMDD HH24:MI')
b_Sunday, 1 ord
from
  (select 'File CUSTOMER' file_type_desc,'11:00' b, 11/24 begin_add,
20/24 sat_add, 5/24 sun_add,'13:00' re,'13:00' e, 2/24 end_add,'03:00'
t, 1 o from dual union
   select 'Schedule LASL' file_type_desc,'13:00' b, 13/24 begin_add,
20/24 sat_add, 5/24 sun_add,'15:00' re,'15:00' e, 2/24 end_add,'03:00'
t, 2 o from dual union
   select 'File EVENTS 192' file_type_desc,'18:00' b, 18/24 begin_add,
20/24 sat_add, 5/24 sun_add,'21:00' re,'21:00' e, 3/24 end_add,'03:00'
t, 3 o from dual union
   select 'File EVENTS 401to406' file_type_desc,'21:00' b, 21/24
begin_add, 20/24 sat_add, 5/24 sun_add,'05:00' re,'05:00' e, 8/24
end_add,'08:00' t, 4 o from dual union
   select 'Schedule EOD' file_type_desc,'21:00' b, 21/24 begin_add,
20/24 sat_add, 5/24 sun_add,'05:00' re,'05:00' e, 8/24 end_add,'08:00'
t, 5 o from dual) b
,(SELECT NEXT_Day(TRUNC(SYSDATE)-4, 'Wednesday')
report_dt,NEXT_Day(TRUNC(SYSDATE)-4, 'Wednesday')-7 Day1
,NEXT_Day(TRUNC(SYSDATE)-4, 'Wednesday')-6 Day2
          ,NEXT_Day(TRUNC(SYSDATE)-4, 'Wednesday')-5
Day3,NEXT_Day(TRUNC(SYSDATE)-4, 'Wednesday')-4
SaturDay,NEXT_Day(TRUNC(SYSDATE)-4, 'Wednesday')-3 SunDay
         ,NEXT_Day(TRUNC(SYSDATE)-4, 'Wednesday')-2
Day4,NEXT_Day(TRUNC(SYSDATE)-4, 'Wednesday')-1 Day5
   FROM Dual) days
) f
,(select file_type_desc, b, e
       ,to_char(day1+begin_add,'YYYYMMDD HH24:MI') b_Day1,
to_char(Day1+begin_add+end_add,'YYYYMMDD HH24:MI') e_Day1
       ,to_char(day2+begin_add,'YYYYMMDD HH24:MI') b_Day2,
to_char(Day2+begin_add+end_add,'YYYYMMDD HH24:MI') e_Day2
       ,to_char(day3+begin_add,'YYYYMMDD HH24:MI') b_Day3,
to_char(Day3+begin_add+end_add,'YYYYMMDD HH24:MI') e_Day3
       ,to_char(SaturDay+begin_add+sat_add,'YYYYMMDD HH24:MI')
b_Saturday, to_char(saturday+begin_add+sat_add+end_add,'YYYYMMDD
HH24:MI') e_Saturday
       ,to_char(SunDay+begin_add+sun_add,'YYYYMMDD HH24:MI') b_Sunday,
to_char(sunday+begin_add+sun_add+end_add,'YYYYMMDD HH24:MI') e_Sunday
       ,to_char(day4+begin_add,'YYYYMMDD HH24:MI') b_Day4,
to_char(Day4+begin_add+end_add,'YYYYMMDD HH24:MI') e_Day4
       ,to_char(day5+begin_add,'YYYYMMDD HH24:MI') b_Day5,
to_char(Day5+begin_add+end_add,'YYYYMMDD HH24:MI') e_Day5
       ,t duration,o
from
  (select 'File CUSTOMER' file_type_desc,'11:00' b, 11/24 begin_add,
20/24 sat_add, 5/24 sun_add,'13:00' re,'13:00' e, 2/24 end_add,'03:00'
t, 1 o from dual union
   select 'Schedule LASL' file_type_desc,'13:00' b, 13/24 begin_add,
20/24 sat_add, 5/24 sun_add,'15:00' re,'15:00' e, 2/24 end_add,'03:00'
t, 2 o from dual union
   select 'File EVENTS 192' file_type_desc,'18:00' b, 18/24 begin_add,
20/24 sat_add, 5/24 sun_add,'21:00' re,'21:00' e, 3/24 end_add,'03:00'
t, 3 o from dual union
   select 'File EVENTS 401to406' file_type_desc,'21:00' b, 21/24
begin_add, 20/24 sat_add, 5/24 sun_add,'05:00' re,'05:00' e, 8/24
end_add,'08:00' t, 4 o from dual union
   select 'Schedule EOD' file_type_desc,'21:00' b, 21/24 begin_add,
20/24 sat_add, 5/24 sun_add,'05:00' re,'05:00' e, 8/24 end_add,'08:00'
t, 5 o from dual) b
,(SELECT NEXT_Day(TRUNC(SYSDATE)-4, 'Wednesday')
report_dt,NEXT_Day(TRUNC(SYSDATE)-4, 'Wednesday')-7 Day1
,NEXT_Day(TRUNC(SYSDATE)-4, 'Wednesday')-6 Day2
          ,NEXT_Day(TRUNC(SYSDATE)-4, 'Wednesday')-5
Day3,NEXT_Day(TRUNC(SYSDATE)-4, 'Wednesday')-4
SaturDay,NEXT_Day(TRUNC(SYSDATE)-4, 'Wednesday')-3 SunDay
         ,NEXT_Day(TRUNC(SYSDATE)-4, 'Wednesday')-2
Day4,NEXT_Day(TRUNC(SYSDATE)-4, 'Wednesday')-1 Day5
   FROM Dual) days) benchmark
where replace(replace(replace(replace(f.file_type_desc,'-begin'),'-end'),'-duration'),'-benchmark')
= benchmark.file_type_desc
order by o,ord, f.file_type_desc
)

stevenbian

你贴那么多代码谁会看啊,自己把to_date去掉看一下不就知道那个文件错了吗.