blog

bochgoch

help!

Wordpress

systems thinking

I like to take a holistic approach to problem solving, systems thinking helps...


What shall we say then?


Sybase SQL to Oracle SQL conversion

For successful conversion of Sybase SQL to Oracle SQL syntax a number of amendments must be made to the SQL. These guidelines should aid you in this process.
These notes are written from the perspective of the migration of legacy Sybase databases to Oracle 9i / 10G databases and therefore may not be accurate in comparision with current version Sybase databases.

Functions

SybaseOracle Description
PATINDEXINSTR PATINDEX and INSTR both return the starting position of a character or string of characters within another string.

SYBASE SYNTAX: INSTR (expression , '%pattern%')
ORACLE SYNTAX: PATINDEX ( '%pattern%' , expression )
For PATINDEX when used to apply a condition within a SELECT statement, as in: 
sum(Order_Value * convert(bit,patindex
('666',substring(Order_Code,1,3))))
...wrap INSTR within a CASE statement. 
sum( CASE WHEN 
INSTR(Order_Code,'666') = 1 
THEN Order ELSE NULL END )
CASECASE ORACLE SYNTAX: 
CASE WHEN condition THEN value
WHEN condition THEN value
...
ELSE value
END
SUBSTRINGSUBSTR Returns the portion of a string expression from the start character for the number of characters specified by the length.

SYBASE SYNTAX: SUBSTRING(expression,start,length)
ORACLE SYNTAX: SUBSTR(expression,start,length)
CHAR_LENGTHLENGTH Returns number of characters in a a string expression.

SYBASE SYNTAX: CHAR_LENGTH(expression)
ORACLE SYNTAX: LENGTH(expression)
n/aLAST_DAY Returns the last date in the month (i.e. 31-Jan-2005) of the date expression.

ORACLE SYNTAX: LAST_DAY(expression)
n/aEXTRACT Extracts a portion of a date as an integer. The following types can be extracted: DAY MONTH YEAR HOUR MINUTE SECOND TIMEZONE_HOUR TIMEZONE_MINUTE TIMEZONE_REGION TIMEZONE_ABBR

ORACLE SYNTAX: EXTRACT(type FROM date_expression)

SELECT EXTRACT(YEAR FROM SYSDATE) * 100 +
       EXTRACT(MONTH FROM SYSDATE) FROM DUAL

Conversion

SybaseOracle Description
CONVERT( INTEGER(etc) ,expression)TO_NUMBER( value ,[format]) When converting strings to numbers,
i.e. 
TO_NUMBER('1768.01', '$9999.999')

...the following options may be included to build [format]:
9Each represents a significant digit. Leading zeros in a number are displayed or treated as blanks.
0Each represents a significant digit to be returned. Leading zeros in a number are displayed or treated as zeros.
$Prefix: puts a dollar sign in front of the number.
BPrefix: returns a zero value as blanks, even if the format element was used to show a leading zero.
MISuffix: places a minus sign (-) after the number if it is negative. For positive values it returns a trailing space, which is different from NULL.
SPrefix: places a plus sign (+) in front of a positive number and a minus sign (-) before a negative number.
PRSuffix: places angle brackets (< and >) around a negative value. For positive values it places leading and trailing spaces around the number.
DSpecifies the location of the decimal point in the returned value. All format elements to the left of the D will format the integer component of the value. All format elements to the right of the D will format the fractional part of the value. The character used for the decimal character is determined by the database initialization parameter NLS_NUMERIC_CHARACTERS.
GSpecifies the location of the group separator (for example, a comma to separate thousands as in 6,734) in the returned value. The character used for the group separator is determined by the database initialization parameter NLS_NUMERIC_CHARACTERS.
CSpecifies the location of the ISO currency symbol in the returned value.
LSpecifies the location of the local currency symbol (such as $) in the returned value.
,(comma) Specifies that a comma be returned in that location in the return value.
.(period) Specifies that a period be returned in that location in the return value.
VMultiplies the number to the left of the V in the format model by 10 raised to the nth power, where n is the number of 9s found after the V in the format model.
EEEESuffix: specifies that the value be returned in scientific notation.
RN/rnSpecifies that the return value be converted to upper- or lowercase Roman numerals. The range of valid numbers for conversion to Roman numerals is between 1 and 3999. The value must be an integer.
CONVERT( varchar, expression)TO_CHAR( value, [format]) When converting dates to strings,
i.e. 
TO_CHAR(sysdate, 'YYYY/MM/DD')

...the following options may be included to build [format]:
YEARYear in words (i.e. 2005 = 'TWO THOUSAND FIVE')
YYYY4-digit year (i.e. 1973)
YYYLast 3 digits of year
YYLast 2 digits of year
YLast 1 digit of year
IYYLast 3 digits of ISO year
IYLast 2 digits of ISO year
ILast 1 digit of ISO year
IYYY4-digit year based on the ISO standard
QQuarter of year (1, 2, 3, 4)
MMMonth (01-12; JAN = 01)
MONAbbreviated name of month
MONTHName of month, padded 9 characters
RMRoman numeral month (I-XII)
WWWeek of year (1-53)
WWeek of month (1-5)
IWISO standard Week of year (1-52 or 1-53)
DDay of week (1-7)
DAYName of day
DDDay of month (1-31)
DDDDay of year (1-366)
DYAbbreviated name of day
JJulian day - the number of days since 01/01/4712BC
HHHour of day (1-12)
HH12Hour of day (1-12)
HH24Hour of day (0-23)
MIMinute (0-59)
SSSecond (0-59)
SSSSSSeconds since midnight (0-86399)
FFFractional seconds
CONVERT( date, expression)TO_DATE( value, [format], [nls_language]) See TO_CHAR for format mask options & syntax.

Temporary Tables

SybaseOracle Description
# tablesGLOBAL TEMPORARY TABLE Oracle differs significantly from Sybase in this respect. A Sybase (SQL Server) # (hash / temporary table) is created in memory and only exists for the duration of the transaction it is created in. A Oracle GLOBAL TEMPORARY TABLE (GTT) is created in the same way as a TABLE, but the data is TEMPORARY and only exists within the GTT for the duration of the transaction that it is inserted into the GTT in.

NULL's

SybaseOracle Description
The handling of NULL values differs between Sybase and Oracle.
In short, in Oracle Null is neither EQUAL to Null nor is it NOT EQUAL (greater or less than) to Null! -- See Example 1 below.
In Sybase, Null is EQUAL to Null -- See Example 2 below.

Example 1 -- Null is neither = Null nor <> Null in Oracle 
      SQL> select * from dual
      D
      -
      X
      
      SQL> select * from dual where null=null
      no rows selected
      
      SQL> select * from dual where null<>null
      no rows selected

Example 2 -- Null = Null in Sybase 
      declare @x integer
      select 'Hello'
      where @x=null
      
       -----
       Hello
      (1 row affected)
This difference necessitates careful handling of comparisons that may include Null's within the Oracle environment. As can be seen in Example 3 below, unhandled comparison of null values will generate errors in otherwise valid code. The solution is to code defensively, most obviously through use of the NVL function and through ensuring that variables are assigned non-null values on definition.

Example 3 -- Null comparison in Oracle 
      set serveroutput on;
      declare 
      x integer;
      y varchar2(5);
      begin
      x:=null;
      select 'Hello' into y from dual
      where x=x;
      dbms_output.put_line(y);
      end;
      
      ORA-01403: no data found
      ORA-06512: at line 6
      
      
      set serveroutput on;
      declare 
      x integer;
      y varchar2(5);
      begin
      x:=1;
      select 'Hello' into y from dual
      where x=x;
      dbms_output.put_line(y);
      end;
      
      Hello
      PL/SQL procedure successfully completed.
      
      
      set serveroutput on;
      declare 
      x integer;
      y varchar2(5);
      begin
      x:=null;
      select 'Hello' into y from dual
      where nvl(x,1)=nvl(x,1);
      dbms_output.put_line(y);
      end;
      
      Hello
      PL/SQL procedure successfully completed.




some sites from the bochgoch stables... More details...