Oralce and DB2 compatible development considerations _oracle

Database compatibility, in the development project process, will inevitably encounter the change database, or later project upgrades, may also encounter this situation, here is a description of the next Oralce and DB2 compatible development considerations.

compatible ORALCE, DB2 development considerations (provided that the DB2 version is 9.7 and the database created after the PLSQL compilation option is turned on):

1. If you use a table field after like, you should unify it to use the Locate function

Such as:
Oralce:
SELECT * from Fw_right a where ' like a.rightid| | ' %';
Compatible writing:
SELECT * from Fw_right a Where locate (' a.rightid ') = 1;
Oralce:
SELECT * from Fw_right a where ' like '% ' | | a.rightid| | ' %';
Compatible writing:
SELECT * from Fw_right a Where locate (' A.rightid ') > 0;

2. Aliases used in views should not have the same name as the current table field

As in the following statement, there is no problem with Oracle, but the "sql0153n" error is reported in DB2:

 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,  ' Yyyymmddhh24miss '), ' Yyyy-mm-dd hh24:mi:ss ') as Start_time,
       to_char (To_date, ' 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 ')

compatible writing:

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 or FETCH A/n ROWS is not allowed in the following cases:

* Outer full Query view
* The outer full query in the return statement of the SQL table function
* Materialized query table definition
* Subqueries not enclosed in parentheses

Otherwise, it will be reported that "sql20211n spec order BY or FETCH" n ROWS is invalid. Error.

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

Compatible writing:

  CREATE OR REPLACE VIEW v_fw_blank_bulletin as
  select * FROM (SELECT ID,   bae001, Operunitid, Operunittype, unitsubt Ype, 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, ' Yyyymmddhh24miss ') and
  (To_char (validto) >= to_char (sysdate, ' Yyyymmddhh24miss ') or validto is null) and
  aae100 = ' 1 '
  ORDER BY ifergency Desc, id desc, duetime desc)

 

Thank you for reading, I hope to help you, thank you for your support for this site!