Main differences in writing DB2 and oracle SQL statements

The main difference between the writing of DB2 and oracle SQL statements is to be honest. ORACLE has spoiled domestic programmers, and the SQL statements in the Code are filled with a large number of ORACLE features. Almost no one knows what the ANSI standard SQL looks like, as a result, the program cannot run without ORACLE. Fortunately, with the popularity of hibernate, the situation has greatly improved DB2 as a choice of many major international customers (it is said that the world's top 500 use DB2, the top 100 are all using DB2), which is really not popular in China and belongs to niche databases, but there is no way to use DB2 for current projects, therefore, I had to face the "ORACLE version code" written by ORACLE developers who were familiar with ORACLE. Many compatibility problems were very high, so I sorted out a list of frequently encountered compatibility problems for your reference, it seems that there are fewer problems recently, I hope this momentum will continue. 1. Data type conversion functions: integer to simplified string to integer string to floating point type conversion function: string to date string to timestamp date to string ORA to_char (1) to_number ('1') to_number ('1. 1 ') to_char (1.1) to_date ('2017-04-26', 'yyyy-mm-dd') to_date ('2017-04-26 08:08:08 ', 'yyyy-MM-DD HH24: MI: ss') to_char (to_date ('1970-04-29 ', 'yyyy-mm-dd'), 'yyyy-mm-dd') DB2 char (1) int ('1') double ('1. 1 ') char (1.1) date ('2017-04-26') to_date ('2017-04-26 08:08:08 ', 'yyyy-MM-DD HH24: MI: ss ') char (date ('1970-04-29 ') compatible with cast (1 as char) cast ('1' as int) no, no. 2. Weak Where condition. oracle: where unsupported FIELD in (integer) is allowed, DB2 does not allow select 'abc' from dual where '1' in (1) to use select 'abc' from sysibm in oracle. sysdummy1 where '1' in (1) errors reported in DB2 oracle: where numeric field = numeric field allowed, DB2 does not allow select 'abc' from dual where '1' = 1. in oracle, select 'abc' from sysibm. sysdummy1 whre '1' = 1 error reported in DB2 3. Support for the replace keyword oracle, DB2 does not support the create or replace statement. 4. The subquery alias ORACLE supports select * from (select 1 from dual) or select * from (select 1 from dual) t DB2 supports select * from (select 1 from sysibm. sysdummy1) t or select * from (select 1 from sysibm. sysdummy1) as t solid compatibility is written in the form of select * from (subquery) t 5, DATE data type difference in oracle date type is also with time, minute, second, but DB2 DATE is only year, month, day, for example, '2017-04-28 'and can be operated directly as a string. to record the time, minute, and second, the TIMESTAMP type must be used in DB2. A common compatibility problem after hibernate is adopted is: if a field defined in the ing file is of Date type <property name = "createTime" type = "java. util. date "> <column name =" CREATE_TIME "length =" 7 "/> </property>: in DB2, this field must be defined as timestamp rather than DATE, otherwise, the right truncation of the string will be reported. For DB2, you can directly use the string to specify the value of the date or time stamp field in the query conditions, for example, where create_date = '2014-04-26 ', where create_timestamp = '2014-04-26 08:08:08', you do not need to use the string to convert the date function 6. If you use JDBC paging, note that rownum is not supported in DB2. For example, to obtain 10 records with the minimum area_id from the masa_area table, the statements are as follows. Note that the alias t is written in ORACLE: select t. * from (select rownum as r1, masa_area. * from masa_area order by area_id) t where t. r1 <= 10 DB2: select t. * from (select rownumber () over () as r1, masa_area. * from masa_area order by area_id) t where t. r1 <= 10 7. decode function is not supported in DB2. compatible writing method is case when 8 and NVL function nvl writing is not supported in DB2, coalesce ORACLE: select NVL (f_areaid, 'null') from masa_user is equivalent to select coalesce (f_areaid, 'null', f_areaid) from masa_user DB2: select coalesce (f_areaid, 'null', f_areaid) from masa_user 9, substr different DB2 substr example: The f_groupCode field of the masa_group table is defined as VARCHAR (100 ), therefore, the following statement will not cause errors. If it is substr (f_groupCode, 1,101), the select * from masa_group where substr (f_groupCode,) error will occur) = '000000' order by f_groupcode has no error in DB2, but select * from masa_group where substr ('000000', 1, 50) = '000000' order by f_groupcode returns an error, the third parameter is exceeded because '200' has been defined as a charater with a length of 6, which is very different from ORACLE. Please note that if you want to get the last one from the first one, the safe solution is not to add the third parameter ORACLE: select substr ('20160301', 1) from dual DB2: select substr ('20160301', 1) from sysibm. no problem with sysdummy1 10. Get the CURRENT operating system date oracle Sysdate DB2 CURRENT DATE