Chinaunix首页 | 论坛 | 博客
  • 博客访问: 142101
  • 博文数量: 43
  • 博客积分: 810
  • 博客等级: 军士长
  • 技术积分: 430
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-03 02:39
文章分类
文章存档

2011年(43)

分类: Oracle

2011-06-14 15:07:58

最近几天在做新系统的监控,其中有个日期表要验证结果数据和当前日期的正确性,写一段比较死板的代码穷举类型来验证所有起止日期
穷举各类型,根据类型验证起止日期和当前日期在对应类型下的起止日期
返回验证结果
如果验证结果不为空且不为0 ,则对应类型的起止日期异常;
如果验证结果为空,则对应类型未在穷举验证内;
 
代码框的代码不能修改吗
  1. select s.inc_type "日期类型",

  2.        s.inc_start "开始日期",

  3.        s.inc_end "结束日期",

  4.        s.case "验证结果"

  5.   from (select distinct eit.inc_type,

  6.                         eit.inc_start,

  7.                         eit.inc_end,

  8.                         case

  9.                           when eit.inc_type = 'D' then --日


  10.                            eit.inc_start - to_char(sysdate - 1, 'yyyymmdd')

  11.                           when eit.inc_type = 'DA1' then --日期往后增量


  12.                            eit.inc_end - to_char(sysdate + 1, 'yyyymmdd')

  13.                           when eit.inc_type = 'DA2' then

  14.                            eit.inc_end - to_char(sysdate + 2, 'yyyymmdd')

  15.                           when eit.inc_type = 'DD1' then--日期往前增量


  16.                            eit.inc_start - to_char(sysdate - 2, 'yyyymmdd')

  17.                           when eit.inc_type = 'DD2' then

  18.                            eit.inc_start - to_char(sysdate - 3, 'yyyymmdd')

  19.                           when eit.inc_type = 'M01' then --月根据每月第一天


  20.                            eit.inc_end -

  21.                            to_char(trunc(sysdate, 'mm'), 'yyyymmdd')

  22.                           when eit.inc_type = 'M' then

  23.                            eit.inc_end -

  24.                            to_char(trunc(sysdate, 'mm'), 'yyyymmdd')

  25.                           when eit.inc_type in ('W0', 'W7') then --周 对比当前日期和本周第几天来确定


  26.                            eit.inc_end -

  27.                            to_char(sysdate - to_char(sysdate, 'd') + 1,

  28.                                    'yyyymmdd')

  29.                           when eit.inc_type = 'W1' then

  30.                            eit.inc_end -

  31.                            to_char(sysdate - to_char(sysdate, 'd') + 2,

  32.                                    'yyyymmdd')

  33.                           when eit.inc_type = 'W2' then

  34.                            eit.inc_end -

  35.                            to_char(sysdate - to_char(sysdate, 'd') + 3,

  36.                                    'yyyymmdd')

  37.                           when eit.inc_type = 'W3' then

  38.                            eit.inc_end -

  39.                            to_char(sysdate - to_char(sysdate, 'd') + 4,

  40.                                    'yyyymmdd')

  41.                           when eit.inc_type = 'W4' then

  42.                            eit.inc_end -

  43.                            to_char(sysdate - to_char(sysdate, 'd') + 5,

  44.                                    'yyyymmdd')

  45.                           when eit.inc_type = 'W5' then

  46.                            eit.inc_end -

  47.                            to_char(sysdate - to_char(sysdate, 'd') + 6,

  48.                                    'yyyymmdd')

  49.                           when eit.inc_type = 'W6' then

  50.                            eit.inc_end -

  51.                            to_char(sysdate - to_char(sysdate, 'd') + 7,

  52.                                    'yyyymmdd')

  53.                           when eit.inc_type like 'Q%' then --季类型 分四季check月是否正确


  54.                            SUBSTR(eit.inc_end, 5, 4) -

  55.                            decode(to_char(sysdate, 'Q'),

  56.                                   '1',

  57.                                   '0101',

  58.                                   '2',

  59.                                   '0401',

  60.                                   '3',

  61.                                   '0701',

  62.                                   '4',

  63.                                   '1101')

  64.                           when eit.inc_type = 'Y' then

  65.                            SUBSTR(eit.inc_end, 1, 4) -

  66.                            to_char(sysdate, 'YYYY')

  67.                         end case

  68.           from etl_inc_time_config eit

  69.          where upper(eit.inc_type) <> 'INIT'

  70.            and ((eit.inc_type like 'W%' and

  71.                 substr(eit.inc_type, 2, 1) < to_char(sysdate, 'd')) or --周取数需到当前周期才生效


  72.                 eit.inc_type not like 'W%')) s

  73.  where s.case <> 0 --结果为0 则数据正常 非0 则与规则不符


  74.     or s.case is null
阅读(1428) | 评论(1) | 转发(0) |
0

上一篇:test2

下一篇:培训-职场实用写作

给主人留下些什么吧!~~

huangw4252011-06-20 16:29:52