Difference between revisions of "DB2 to Oracle maps"

From The University of Akron Support Wiki
Jump to: navigation, search
(No difference)

Revision as of 21:30, 23 April 2008

PSV9 > Oracle Knowledge Base

Function mappings

STRIP to TRIM

The DB2 Function STRIP needs converted to the Oracle Function TRIM:

STRIP(column_name, L, ‘ ‘ ) to TRIM(LEADING ‘ ‘ from column_name) – trim leading blanks from the column
STRIP(column_name, T, ‘ ‘ ) to TRIM(TRAILING ‘ ‘ from column_name) – trim trailing blanks from the column
STRIP(column_name, B, ‘ ‘ ) to TRIM(BOTH ‘ ‘ from column_name) – trim both leading and trailing blanks from the column

VALUE to COALESCE

Convert the VALUE function to COALESCE (just change VALUE to COALESCE).

SELECT 
NMS.LAST_NAME,
NMS.FIRST_NAME, 
substr(NMS.MIDDLE_NAME,1),   
PDT.SEX,
PDT.BIRTHDATE,
VALUE(DIV.ETHNIC_GROUP,'U'), 
VALUE(EMA.EMAIL_ADDR,' '),   

PDT.FERPA
  
FROM PS_NAMES NMS
...

becomes

SELECT 
NMS.LAST_NAME,
NMS.FIRST_NAME, 
substr(NMS.MIDDLE_NAME,1),   
PDT.SEX,
PDT.BIRTHDATE,
COALESCE(DIV.ETHNIC_GROUP,'U'), 
COALESCE(EMA.EMAIL_ADDR,' '),   

PDT.FERPA
  
FROM PS_NAMES NMS
...

INT/INTEGER to TO_NUMBER

Use of column function INTEGER or INT in DB2 should be converted to TO_NUMBER in Oracle.

TRANSLATE DIFFERENCES

Use of column function TRANSLATE(column name, to-string, from-string, pad-character) in DB2 is a little different in Oracle .

In Oracle, the to-string ('100') and the from-string ('YN ') is swapped.

TRANSLATE(column name,'100', 'YN ') in DB2 will take the value in column name and convert 'YN ' to '100'
TRANSLATE(column name,'YN ', '100') in Oracle will take the value in column name and convert 'YN ' to '100'.

Other Stuff

SYSDUMMY1 to DUAL

Convert your

SELECT ... FROM SYSIBM.SYSDUMMY1 

to

SELECT ... FROM DUAL.

DUAL in Oracle is like SYSDUMMY1 in DB2; it contains one row and is a simple way in SQL to gather information from the database special registers such as dates/times.