兼容oralce、db2开发注意事项

 

兼容oralce、db2开发注意事项（前提是db2版本是9.7，且是开启PLSQL编译选项之后创建的数据库)：

 1. 在like 之后若使用了表字段，应统一改成使用locate函数
  如：
  oralce写法：
   select * from fw_right a where '03' like a.rightid||'%';
  兼容写法：
   select * from fw_right a where locate('03',a.rightid) = 1;

  oralce写法：
   select * from fw_right a where '03' like '%'||a.rightid||'%';
  兼容写法：
   select * from fw_right a where locate('03',a.rightid) > 0;

 2. 视图中使用的别名不应该与当前表字段同名
    如以下语句，在oracle中不会有问题，但在db2中会报"SQL0153N"错误：
    e.g: 
    CREATE OR REPLACE VIEW V_WF_TODOLIST AS
         select c.process_def_id, c.process_def_name, a.action_def_id,
             a.work_item_id,   a.bae007,           a.action_def_name,
             a.state,          a.pre_wi_id,        a.work_type,
             a.operid,         a.x_oprator_ids,    b.process_key_info,
             to_char(to_date(a.start_time,   'yyyymmddhh24miss'),'yyyy-mm-dd hh24:mi:ss') as start_time,
             to_char(to_date(a.complete_time,'yyyymmddhh24miss'),'yyyy-mm-dd hh24:mi:ss') as complete_time,
             a.filter_opr, a.memo,a.bae002,a.bae003, a.bae006,c.x_action_def_ids
  from wf_work_item a, wf_process_instance b, wf_action_def c
    where a.action_def_id  = c.action_def_id
    and b.process_def_id = c.process_def_id
    and a.bae007 = b.bae007
    and a.state in('0','2')
   
    兼容写法：
    CREATE OR REPLACE VIEW V_WF_TODOLIST AS
    select c.process_def_id, c.process_def_name, a.action_def_id,
      a.work_item_id,   a.bae007,           a.action_def_name,
      a.state,          a.pre_wi_id,        a.work_type,
      a.operid,         a.x_oprator_ids,    b.process_key_info,
      to_char(to_date(a.start_time,   'yyyymmddhh24miss'),'yyyy-mm-dd hh24:mi:ss') as start_time_0,
      to_char(to_date(a.complete_time,'yyyymmddhh24miss'),'yyyy-mm-dd hh24:mi:ss') as complete_time_0,
      a.filter_opr, a.memo,a.bae002,a.bae003, a.bae006,c.x_action_def_ids
     from wf_work_item a, wf_process_instance b, wf_action_def c
     where a.action_def_id  = c.action_def_id
      and b.process_def_id = c.process_def_id
      and a.bae007 = b.bae007
      and a.state in('0','2')
     
   3.在下列情况下不允许 ORDER BY 或 FETCH FIRST n ROWS ONLY：
  *  外层全查询视图
  *  "SQL 表函数"的 RETURN 语句中的外层全查询
  *  具体化查询表定义
  *  未用圆括号括起来的子查询
  否则会报"SQL20211N  规范 ORDER BY 或 FETCH FIRST n ROWS ONLY 无效。"错误.
  
  e.g:
  oralce写法：
    CREATE OR REPLACE VIEW V_FW_BLANK_BULLETIN as
    select id,     bae001,  operunitid, operunittype, unitsubtype, ifergency,
        title,  content, digest,    duetime,      validto,     aae100,
        bae006, bae002,  bae003,    id as colid,
        substr(digest,1,20) as digest2
     from fw_bulletin
    where duetime <= to_char(sysdate,'yyyymmddhh24miss')
      and (to_char(validto) >= to_char(sysdate,'yyyymmddhh24miss') or validto is null)
      and aae100 ='1'
    order by ifergency desc, id desc,  duetime desc
 
     兼容写法：
   CREATE OR REPLACE VIEW V_FW_BLANK_BULLETIN as
   select * from (select id,     bae001,  operunitid, operunittype, unitsubtype, ifergency,
      title,  content, digest,    duetime,      validto,     aae100,
      bae006, bae002,  bae003,    id as colid,
      substr(digest,1,20) as digest2
   from fw_bulletin
   where duetime <= to_char(sysdate,'yyyymmddhh24miss')
   and (to_char(validto) >= to_char(sysdate,'yyyymmddhh24miss') or validto is null)
   and aae100 ='1'
   order by ifergency desc, id desc,  duetime desc)