The main differences between DB2 and ORACLE SQL notation

The main differences between DB2 and ORACLE SQL notation

To be honest, Oracle has spoiled the country's programmers, and SQL in the code is flooded with Oracle features, and almost no one knows what ANSI's standard SQL looks like, leaving the program out of Oracle at all, and the situation has changed dramatically with hibernate. DB2 as the choice of many international major customers (said the world's top 500 80% with DB2, the top 100 are all using DB2), in the domestic really very not popular, belonging to the small database, but no way, now the project to use DB2, so have to face familiar with Oracle's development colleagues write " ORACLE version of the code ", a lot of compatibility issues are very big, so the collation of a common list of compatibility issues for everyone to refer to, seemingly recent problems, I hope this momentum can continue 1. Data type conversion function Integral type to character type String to reshape string-to-float float-to-string String to date String Turn timestamp Date to String ORACLE To_char (1) To_number (' 1 ') To_number (' 1.1 ') TO_CHAR (1.1) To_date (' 2007-04-26 ', ' yyyy-mm-dd ') To_date (' 2007-04-26 08:08:08 ', ' yyyy-mm-dd HH24:MI:SS ') To_char (to_date (' 2007-04-29 ', ' yyyy-mm-dd '), ' yyyy-mm-dd ') db2 char (1) int (' 1 ') double (' 1.1 ') date (' 2007-04-26 ') to_date (' 2007-04-26 08:08:08 ', ' yyyy-mm-dd HH24:MI:SS ') char (Date (' 2007-04-29 ')) Compatible wording Cast (1 as Char) Cast (' 1 ' as int) No No No Compatible No 2, where condition weak type judgment Oracle:where character field in (reshape) is allowed, DB2 not allowed Select ' ABC ' from dual where ' 1 ' in (1) is available under Oracle Select ' abc ' from SYSIBM.SYSDUMMY1 where ' 1 ' in (1) error in DB2 Oracle:where character field = Numeric field allowed, DB2 not allowed Select ' ABC ' from dual where ' 1 ' =1 is available under Oracle Select ' abc ' from SYSIBM.SYSDUMMY1 whre ' 1 ' =1 error in DB2 3. Replace keyword Oracle support, DB2 does not support the Create or replace statement is illegal under DB2 4. Sub-query aliases 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-compatible notation is select * FROM (subquery) t 5. Differences in date data types The date type in Oracle is also with sometimes seconds, but DB2 the date is only a month and a day, such as ' 2007-04-28 ', and can be directly manipulated as a string, DB2 must be recorded in the timestamp- One common compatibility issue with Hibernate is: If a field is defined in the mapping file, the date type <property name= "Createtime" type= "Java.util.Date" > <column name= "Create_time" length= "7"/> </property> Under DB2, this field must be defined as timestamp and cannot be defined as date, otherwise the right truncation of the string is reported For DB2, you can specify the value of a date or timestamp type field directly in the query condition, such as where create_date = ' 2007-04-26 ', where Create_timestamp = ' 2007-04-26 08:08:08 ' Without using the string to date function 6, the processing of pagination If you are using JDBC paging, note that rownum is not supported in DB2, such as getting the area_id smallest 10 records from the Masa_area table, the statements are as follows, and note that the alias T writing method 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 () + () as R1, masa_area.* from Masa_area Order by area_id) T where t.r1<=10 7. Decode function The decode function is not supported in DB2, and a compatible notation is used when 8. Nvl function NVL written in DB2 is not supported, compatible with the wording is the use of coalesce Oracle:select NVL (f_areaid, ' empty ') from Masa_user equals select COALESCE (f_areaid, ' empty ', f_areaid) from Masa_user Db2:select COALESCE (f_areaid, ' empty ', f_areaid) from Masa_user 9, the different substr DB2 substr examples are as follows: The F_groupcode field of the Masa_group table is defined as varchar (100), so the following statement does not go wrong, and if it is substr (f_groupcode,1,101) it goes wrong. SELECT * from Masa_group where substr (f_groupcode,1,50) = ' 001006 ' ORDER by F_groupcode There's nothing wrong with DB2, but SELECT * from Masa_group where substr (' 001006 ', 1, +) = ' 001006 ' ORDER by F_groupcode On the error, said the third parameter overrun This is because ' 001006 ' has been defined as a charater with a length of 6 This is a lot different from Oracle, please note that If you want to take the last one from the first place, the safe way is not to add a third argument Oracle:select substr (' 123456 ', 1) from dual Db2:select substr (' 123456 ', 1) from sysibm.sysdummy1 There's no problem. 10. Get the current date of the operating system ORACLE sysdate DB2 Current DATE

The main differences between DB2 and ORACLE SQL notation