新足迹 › 论坛 › 兴趣爱好区 › IT与科技 › IT专业论坛 › 请教一个ORA错误，关于有效日期格式的。 ...

精华好帖回顾
· 毛毛虫来啦~~~ (2007-10-16) 紫雪花	· Amy的找工作业 (2008-8-15) amy_guo2008
· 双城记－伦敦与巴黎（图片更新到D4 -_____-b) (2011-10-10) 老猫晒太阳	· 悠悠五岁了, 弟弟三岁啰（加哥俩新照片了） (2005-8-5) 童心的魔镜

查看: 1015|回复: 2

请教一个ORA错误，关于有效日期格式的。 [复制链接]

hfwang

头像被屏蔽

禁止发言

发表于 2013-10-2 20:24 |显示全部楼层

此文章由 hfwang 原创或转贴，不代表本站立场和观点，版权归 oursteps.com.au 和作者 hfwang 所有！转贴必须注明作者、出处和本声明，并保持内容完整

本帖最后由 hfwang 于 2013-10-2 20:31 编辑

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

头像被屏蔽

禁止发言

发表于 2013-10-2 20:25 |显示全部楼层

此文章由 hfwang 原创或转贴，不代表本站立场和观点，版权归 oursteps.com.au 和作者 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

金靴族

发表于 2013-10-2 22:45 |显示全部楼层

此文章由 stevenbian 原创或转贴，不代表本站立场和观点，版权归 oursteps.com.au 和作者 stevenbian 所有！转贴必须注明作者、出处和本声明，并保持内容完整

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

返回列表

		自动登录	找回密码
密码			注册

精华好帖回顾

请教一个ORA错误，关于有效日期格式的。 [复制链接]

发表回复