Supported SQL Syntax |
Styling Conventions Used Supported Oracle Select Syntax Supported SQL Server Select Syntax Supported IBM DB2 Select Syntax Supported MySQL Select Syntax Supported PostgreSQL Select Syntax Supported Sybase Select Syntax Supported Oracle Insert Syntax Supported SQL Server Insert Syntax Supported DB2 Insert Syntax Supported MySQL Insert Syntax Supported PostgreSQL Insert Syntax Supported Sybase Insert Syntax Supported Oracle Update Syntax Supported SQL Server Update Syntax Supported DB2 Update Syntax Supported MySQL Update Syntax Supported PostgreSQL Update Syntax Supported Sybase Update Syntax Supported Oracle Delete Syntax Supported SQL Server Delete Syntax Supported DB2 Delete Syntax Supported MySQL Delete Syntax Supported PostgreSQL Delete Syntax Supported Sybase Delete Syntax Supported Oracle Create Syntax Supported SQL Server Create Syntax Supported DB2 Create Syntax Supported MySQL Create Syntax Supported PostgreSQL Create Syntax Supported Sybase Create Syntax Supported GRANT statement syntax in all databases Supported MySQL LOAD statement syntax Supported Oracle CREATE SYNONYM syntax Supported COMMIT statement in all databases Supported MySQL AUTOCOMMIT statement Supported Oracle hierarchical query clause Oracle SQL to Teradata SQL conversion features List of Oracle functions converted to Teradata Common Keywords handled across all databases Common Functions supported across all databases

### Styling Conventions Used
CONVENTIONS | MEANING | EXAMPLE
Square Brackets [ ] | Indicates that the enclosed arguments are optional. | DIGITS [. PRECISION ]
Braces followed by plus sign ( )+ | Indicates that the enclosed arguments can occur more than once | ( DIGIT )+
Braces followed by star sign ( )* | Indicates that the enclosed arguments can occur zero or more times | SELECT column_name ( , column_name)* FROM table_name
Question Mark ? | Indicates that the Question mark "?" preceded by an argument is optional. Equivalent to Square Brackets[ ] | <INTEGER> ( "." <INTEGER> )?
Vertical Line | | Vertical Line indicates choices | (DISTINCT | ALL | UNIQUE )
UPPER CASE | Uppercase text indicates case-insensitive filenames or directory names, commands, command keywords, initializing parameters, data types. It need not be in uppercase. | SELECT column_name FROM table_name
lower case | Lowercase text indicates executables and sample user-supplied elements. Such elements include database names, net service names and connect identifiers, as well as user-supplied database objects, structures, column names, packages, classes, user names, roles, program units and parameter values. | DELETE FROM table_name WHERE condition
### Supported Oracle Select Syntax subquery [order_by_clause] [ for_update_clause]
subquery | = | SELECT [ DISTINCT | UNIQUE | ALL ] ( (column_clause ( "," column_clause)* ) | "*") FROM table_expression_clause [where_clause ] [ hierarchical_query_clause ] [ group_by_clause ] [ having_clause ] [ set_operator_clause ]
order_by_clause | = | ORDER BY (order_item_clause) ( "," order_item_clause )*
for_update_clause | = | FOR UPDATE OF (table_column) ("," table_column) [ NO WAIT ]
column_clause | = | ( expression | schema_name.(table_name | view_name).* | (table_name | view_name).* | schema_name.( table_name | view_name ).columnname | ( table_name | view_name ).column_name | column_name ) [ [ AS ] alias_name ]
table_expression_clause | = | ( table_expression | subquery | schema_name.(table_name | view_name) | (table_name | view_name) ) [ AS alias_name ] ( "," ( table_expression | subquery | schema_name.(table_name | view_name) | (table_name | view_name) ) [ AS alias_name ] )*
where_clause | = | WHERE condition [outer_join]
hierarchical_query_clause | = | [ START WITH expression ] CONNECT BY expression
group_by_clause | = | GROUP BY ( expression | CUBE(expressionlist) | ROLLUP(expressionlist) ) ( "," ( expression | CUBE(expressionlist) | ROLLUP(expressionlist) ) )*
having_clause | = | HAVING condition
set_operator_clause | = | ( UNION [ALL] | INTERSECT | MINUS ) "(" subquery ")"
order_item_clause | = | ( expression | position | columnalias) [ ASC | DESC | NULLS FIRST | NULLS LAST ]
table_expression | = | TABLE "(" collection_expression ")"
### Supported SQL Server Select Syntax subquery [ order_by_clause ] [ for_update_clause ]
subquery | = | SELECT [ DISTINCT | ALL ] [ select_qualifier ] ( ( column_clause ( "," column_clause)* ) | "*" ) FROM table_expression_clause [ where_clause ] [ group_by_clause] [ having_clause ] [ set_operator_clause ]
select_qualifier | = | TOP top_value PERCENT
order_by_clause | = | ORDER BY (order_item_clause) ( "," order_item_clause )*
for_update_clause | = | FOR UPDATE OF (table_column) ("," table_column) [ NO WAIT ]
column_clause | = | ( expression | schema_name.(table_name | view_name).* | (table_name | view_name).* | schema_name.( table_name | view_name ).columnname | ( table_name | view_name ).column_name | column_name ) [ [ AS ] alias_name ]
table_expression_clause | = | (subquery | schema_name.(table_name | view_name) | (table_name | view_name) ) [ AS alias_name ] ( "," ( subquery | schema_name.(table_name | view_name) | (table_name | view_name) ) [ AS alias_name ] )* [ join_clause ]
where_clause | = | WHERE condition [outer_join]
group_by_clause | = | GROUP BY ( expression [ WITH CUBE | WITH ROLLUP ] ( "," ( expression [ WITH CUBE | WITH ROLLUP ] ) )*
having_clause | = | HAVING condition
set_operator_clause | = | UNION [ALL] "(" subquery ")"
order_item_clause | = | ( expression | position | columnalias) [ ASC | DESC ]
join_clause | = | [ NATURAL | CROSS | INNER | (LEFT | RIGHT) OUTER ]JOIN table_name [ [ AS ] alias_name ] [ (ON join_expression) ]
### Supported IBM DB2 Select Syntax subquery [ order_by_clause ] [ fetch_clause ]
subquery | = | SELECT [ DISTINCT | ALL ] ( (column_clause ( "," column_clause)* ) | "*" ) FROM table_expression_clause [ where_clause ] [ group_by_clause] [ having_clause ] [ set_operator_clause ]
order_by_clause | = | ORDER BY (order_item_clause) ( "," order_item_clause )*
fetch_clause | = | FETCH FIRST fetch_value ( ROW | ROWS ) ONLY
column_clause | = | ( expression | schema_name.(table_name | view_name).* | (table_name | view_name).* | schema_name.( table_name | view_name ).columnname | ( table_name | view_name ).column_name | column_name ) [ [ AS ] alias_name ]
table_expression_clause | = | ( table_expression | schema_name.(table_name | view_name) | (table_name | view_name) ) [ AS alias_name ] ( "," ( table_expression | schema_name.(table_name | view_name) | (table_name | view_name) ) [ AS alias_name ] )* [ join_clause ]
where_clause | = | WHERE condition
group_by_clause | = | GROUP BY ( ( expression | CUBE(expressionlist) | ROLLUP(expressionlist) )
|  | ( "," ( expression | CUBE(expressionlist) | ROLLUP(expressionlist) ) )* ) | GROUPING SETS(grouping_list)
having_clause | = | HAVING condition
set_operator_clause | = | ( UNION [ALL] | INTERSECT [ALL] | EXCEPT [ALL] ) "(" subquery ")"
order_item_clause | = | ( expression | position | columnalias) [ ASC | DESC ]
table_expression | = | TABLE "(" (expression | subquery | functioncalls) ")"
join_clause | = | [ NATURAL | CROSS | INNER | (LEFT | RIGHT) OUTER ]JOIN table_name [ [ AS ] alias_name ] [ (ON join_expression) ]
### Supported MySQL Select Syntax
select_query | = | SELECT [ DISTINCT | ALL ] [ select_qualifier ] [ into_clause ] ( ( column_clause ( "," column_clause )* ) | "*" ) [ FROM table_expression_clause ] [ where_clause ] [ group_by_clause ] [ having_clause ] [ order_by_clause ] [ limit_clause ]
select_qualifier | = | ( HIGH_PRIORITY | SQL_BIG_RESULT | SQL_SMALL_RESULT | STRAIGHT_JOIN )
into_clause | = | INTO ( OUTFILE | DUMPFILE ) file_name
column_clause | = | ( expression | schema_name.table_name.* | table_name.* | schema_name.table_name.columnname | table_name.column_name | column_name ) [ [ AS ] alias_name ]
table_expression_clause | = | ( schema_name.table_name | table_name | schema_name.view_name | view_name ) [ AS alias_name ] ( "," ( schema_name.table_name | table_name | schema_name.view_name | view_name ) [ AS alias_name ])* [join_clause ]
where_clause | = | WHERE condition
group_by_clause | = | GROUP BY expression ( "," expression )*
having_clause | = | HAVING condition
order_by_clause | = | ORDER BY (order_item_clause) ( "," order_item_clause )*
limit_clause | = | LIMIT (value | ALL) [ "," value ]
join_clause | = | [ NATURAL | CROSS | INNER ] JOIN table_name [ [ AS ] alias_name ] [ (ON join_expression) | USING "(" using_list ")" ]
order_item_clause | = | ( expression | position | columnalias) [ ASC | DESC ]
### Supported PostgreSQL Select Syntax subquery [ order_by_clause ] [ limit_clause ]
subquery | = | SELECT [ DISTINCT [ ON "(" distinct_list ")" ] | ALL ] [ into_clause ] ( column_clause | "*" ) ( "," ( column_clause | "*" ) )* [ FROM table_expression_clause ] [ where_clause ] [ group_by_clause] [ having_clause ] [ set_operator_clause ]
order_by_clause | = | ORDER BY (order_item_clause) ( "," order_item_clause )*
limit_clause | = | LIMIT (limit_value | ALL) [ ("," | OFFSET) offset_value ] | OFFSET offset_value LIMIT (limit_value | ALL)
into_clause | = | INTO [ TEMP | TEMPORARY ] table_name
column_clause | = | ( expression | schema_name.(table_name | view_name).* | (table_name | view_name).* | schema_name.( table_name | view_name ).columnname | ( table_name | view_name ).column_name | column_name ) [ [ AS ] alias_name ]
table_expression_clause | = | ( schema_name.(table_name | view_name) | (table_name | view_name) ) [ AS alias_name ] ( "," ( schema_name.(table_name | view_name) | (table_name | view_name)) [ AS alias_name ] )* [ join_clause ]
where_clause | = | WHERE condition
group_by_clause | = | GROUP BY expression ( "," expression )*
having_clause | = | HAVING condition
set_operator_clause | = | ( UNION [ALL] | INTERSECT | EXCEPT ) "(" subquery ")"
order_item_clause | = | ( expression | position | columnalias) [ ASC | DESC | USING operator ]
join_clause | = | [ NATURAL | CROSS | INNER | (LEFT | RIGHT) OUTER ]JOIN table_name [ [ AS ] alias_name ] [ (ON join_expression) | USING "(" using_list ")" ]
### Supported Sybase Select Syntax subquery [ order_by_clause ] [ for_update_clause ]
subquery | = | SELECT [ DISTINCT | ALL ] [ select_qualifier ] ( ( column_clause ( "," column_clause)* ) | "*" ) FROM table_expression_clause [ where_clause ] [ group_by_clause] [ having_clause ] [ set_operator_clause ]
select_qualifier | = | TOP top_value PERCENT
order_by_clause | = | ORDER BY (order_item_clause) ( "," order_item_clause )*
for_update_clause | = | FOR UPDATE OF (table_column) ("," table_column) [ NO WAIT ]
column_clause | = | ( expression | schema_name.(table_name | view_name).* | (table_name | view_name).* | schema_name.( table_name | view_name ).columnname | ( table_name | view_name ).column_name | column_name ) [ [ AS ] alias_name ]
table_expression_clause | = | (subquery | schema_name.(table_name | view_name) | (table_name | view_name) ) [ AS alias_name ] ( "," ( subquery | schema_name.(table_name | view_name) | (table_name | view_name) ) [ AS alias_name ] )* [ join_clause ]
where_clause | = | WHERE condition [outer_join]
group_by_clause | = | GROUP BY ( expression [ WITH CUBE | WITH ROLLUP ] ( "," ( expression [ WITH CUBE | WITH ROLLUP ] ) )*
having_clause | = | HAVING condition
set_operator_clause | = | UNION [ALL] "(" subquery ")"
order_item_clause | = | ( expression | position | columnalias) [ ASC | DESC ]
join_clause | = | [ NATURAL | CROSS | INNER | (LEFT | RIGHT) OUTER ]JOIN table_name [ [ AS ] alias_name ] [ (ON join_expression) ]
### Supported Oracle Insert Syntax
insert | = | INSERT [INTO] [ [ database_name.]owner.] (table_name | view_name)
|  | [ PARTITION partition_name | SUBPARTITION subpartition_name ] [ column_list ] ( VALUES "(" values_list ")" [ RETURNING expression_list INTO variable_list ] | subquery [ WITH ( READ ONLY | CHECK OPTION [ CONSTRAINT constraint_name ]) ] )
column_list | = | column_name ( "," column_name)*
values_list | = | (value | NULL) ( "," (value | NULL))*
expressionlist | = | expression ("," expression)*
variable_list | = | variable_name ("," variable_name)*
### Supported SQL Server Insert Syntax
insert | = | INSERT [ INTO ] [ [ database_name.] owner.] ( table_name | view_name ) [ column_list ] ( [ DEFAULT ] VALUES | list_of_values | subquery )
column_list | = | ( column_name ) ("," column_name)*
list_of_values | = | (value | NULL) ("," (value | NULL))*
### Supported DB2 Insert Syntax
insert | = | INSERT INTO [ [ database_name.]owner.] (table_name | view_name) [ column_list ] ( VALUES values_list | [ WITH common_table_expression ] sub_query )
column_list | = | column_name ( "," column_name)*
values_list | = | (values_with_braces | values _without_braces)
values_with_braces | = | "(" (expression | NULL | DEFAULT ) ( "," ( expression | NULL | DEFAULT))* ")"
values_without_braces | = | (expression | NULL | DEFAULT ) ( "," ( expression | NULL | DEFAULT))*
common_table_expression | = | (table_expression) ("," table_expression)*
### Supported MySQL Insert Syntax
insert | = | INSERT [ LOW_PRIORITY | DELAYED ] [ IGNORE ] [ INTO ] [ database_name. ] owner .] ( table_name | view_name ) [ column_list ] ( VALUES values_list | select_query | SET column_value_list )
column_list | = | ( column_name ) ( "," column_name)*
values_list | = | (value | NULL) ( "," (value | NULL))*
column_value_list | = | ( column_name "=" value_name ) ( "," column_name "=" value_name )*
### Supported PostgreSQL Insert Syntax
insert | = | INSERT INTO [ [ database_name.]owner.] (table_name | view_name) [ column_list ] ( DEFAULT VALUES | VALUES "(" values_list ")" | sub_query )
column_list | = | column_name ( "," column_name)*
values_list | = | (value | NULL) ( "," (value | NULL))*
### Supported Sybase Insert Syntax
insert | = | INSERT [ INTO ] [ [ database_name.] owner.] ( table_name | view_name ) [ column_list ] ( [ DEFAULT ] VALUES | list_of_values | subquery )
column_list | = | ( column_name ) ("," column_name)*
list_of_values | = | (value | NULL) ("," (value | NULL))*
### Supported Oracle Update Syntax
update | = | UPDATE [ schema. ] ( view_name | table_name [ @database_link ] ( [ PARTITION partition ] | [ SUBPARTITION subparitition_name ] ) | subquery [ WITH (READ ONLY | CHECK OPTION [ CONSTRAINT constraint_name ] ] SET ( set_values | subquery | VALUE value ) WHERE conditions | CURRENT OF cursor_name RETURNING expression_list into variable_list
set_values | = | (column_name " = " expression) ("," column_name "=" expression)*
expression_list | = | (expression) ("," expression)
variable_list | = | (variable_name) ( "," variable_name)
### Supported SQL Server Update Syntax
update | = | UPDATE ( table_name | view_name ) [ WITH (table_hints) ] SET (set_values) ("," set_values) [ FROM table_list ] WHERE ( conditions | CURRENT OF [GLOBAL] cursor_name) [ OPTION ( query_hints ) ]
table_hints | = | (table_hint) ( "," table_hint )
set_values | = | ( column_name | variable_name ) "=" ( DEFAULT | expression | NULL )
table_list | = | ( table_name ) ( "," table_name )
query_hints | = | ( query_hint ) ("," query_hint )
### Supported DB2 Update Syntax
update | = | UPDATE ( table_name | view_name ) [ AS alias_name ] SET (assignment_clause) ("," assignment_clause)* WHERE conditions
assignment_clause | = | column_name "=" (expression | NULL | DEFAULT) | column_name "=" "(" (expression | NULL | DEFAULT ) ")" | column_name "=" "(" subquery ")"
### Supported MySQL Update Syntax
update | = | UPDATE [ LOW PRIORITY ] table_name SET (column_name | variable_name ) = ( DEFAULT | expression) WHERE conditions [ LIMIT integer ]
### Supported PostgreSQL Update Syntax
update | = | UPDATE [ ONLY ] table_name SET (assignment_clause) ("," assignment_clause)* [ FROM from_list ] [ WHERE condition ]
assignment_clause | = | column_name = expression
from_list | = | table_name ("," table_name)*
### Supported Sybase Update Syntax
update | = | UPDATE ( table_name | view_name ) [ WITH (table_hints) ] SET (set_values) ("," set_values) [ FROM table_list ] WHERE ( conditions | CURRENT OF [GLOBAL] cursor_name) [ OPTION ( query_hints ) ]
table_hints | = | (table_hint) ( "," table_hint )
set_values | = | ( column_name | variable_name ) "=" ( DEFAULT | expression | NULL )
table_list | = | ( table_name ) ( "," table_name )
query_hints | = | ( query_hint ) ("," query_hint )
### Supported Oracle Delete Syntax
delete | = | DELETE FROM [schema.] (table_name | view_name ) [ (PARTITION partition_name) | (SUBPARTITION subpartition_name) ] [ WHERE clause ] [ subquery WITH ( READ ONLY | CHECK OPTION [ CONSTRAINT constraint_name ] ) ] [ RETURNING expression_list INTO variable_list ]
expression_list | = | (expression) ("," expression)
variable_list | = | (variable_name) ("," variable_name)
### Supported SQL Server Delete Syntax
delete | = | DELETE [ FROM ] [ owner. ] (table_name | view_name ) [ WITH query_hints ] [ FROM table_sources ] [ WHERE clause | [ CURRENT OF [ GLOBAL ] cursor_name ] ] [ OPTION query_hints ]
query_hints | = | (query_hint) ("," query_hint)
table_sources | = | (table_name) ("," table_name)
### Supported DB2 Delete Syntax
delete | = | DELETE FROM (table_name | view_name) [ AS alias_name ] [ WHERE condition ]
### Supported MySQL Delete Syntax
delete | = | DELETE [ LOW_PRIORITY ] FROM table_name [ WHERE clause ] [ LIMIT rows ]
### Supported PostgreSQL Delete Syntax
delete | = | DELETE FROM [ ONLY ] table_name [WHERE condition]
### Supported Sybase Delete Syntax
delete | = | DELETE [ FROM ] [ owner. ] (table_name | view_name ) [ WITH query_hints ] [ FROM table_sources ] [ WHERE clause | [ CURRENT OF [ GLOBAL ] cursor_name ] ] [ OPTION query_hints ]
query_hints | = | (query_hint) ("," query_hint)
table_sources | = | (table_name) ("," table_name)
### Supported Oracle Create Syntax
create | = | CREATE [OR REPLACE] (table_name | view_name) [ "(" create_column ")"] [ AS ] select_query_statement[ WITH CHECK OPTION ]
create_column | = | [columnName [datatype] [DEFAULT default_value] [NOT NULL] [constraint_clause]]
constraint_clause | = | primary_constraint | foreign_Constraint | default_constraint | check_constraint
### Supported SQL Server Create Syntax
create | = | CREATE [OR REPLACE] (table_name | view_name) [ "(" create_column ")"] [ AS ] select_query_statement[ WITH CHECK OPTION ]
create_column | = | [columnName [datatype] [DEFAULT default_value] [NOT NULL] [constraint_clause]]
constraint_clause | = | primary_constraint | foreign_Constraint | default_constraint | check_constraint
### Supported DB2 Create Syntax
create | = | CREATE [OR REPLACE] (table_name | view_name) [ "(" create_column ")"] [ AS ] select_query_statement[ WITH CHECK OPTION ]
create_column | = | [columnName [datatype] [DEFAULT default_value] [NOT NULL] [constraint_clause]]
constraint_clause | = | primary_constraint | foreign_Constraint | default_constraint | check_constraint
### Supported MySQL Create Syntax
create | = | CREATE [OR REPLACE] (table_name | view_name) [ "(" create_column ")"] [ AS ] select_query_statement[ WITH CHECK OPTION ]
create_column | = | [columnName [datatype] [DEFAULT default_value] [NOT NULL] [constraint_clause]]
constraint_clause | = | primary_constraint | foreign_Constraint | default_constraint | check_constraint
### Supported PostgreSQL Create Syntax
create | = | CREATE [OR REPLACE] (table_name | view_name) [ "(" create_column ")"] [ AS ] select_query_statement[ WITH CHECK OPTION ]
create_column | = | [columnName [datatype] [DEFAULT default_value] [NOT NULL] [constraint_clause]]
constraint_clause | = | primary_constraint | foreign_Constraint | default_constraint | check_constraint
### Supported Sybase Create Syntax
create | = | CREATE [OR REPLACE] (table_name | view_name) [ "(" create_column ")"] [ AS ] select_query_statement[ WITH CHECK OPTION ]
create_column | = | [columnName [datatype] [DEFAULT default_value] [NOT NULL] [constraint_clause]]
constraint_clause | = | primary_constraint | foreign_Constraint | default_constraint | check_constraint
### Supported GRANT statement syntax in all databases
grant | = | GRANT (ALL [PRIVILEGES] | SELECT | UPDATE | INSERT | DELETE | EXECUTE | UPDATE | REFERENCES) ON (table_name | view_name) TO (PUBLIC | user_name) [ WITH GRANT OPTION ] [ AS ] (group_name | role_name)
### Supported MySQL LOAD statement syntax
load | = | LOAD DATA LOCAL INFILE (file_name) TERMINATED BY (termination_character)
### Supported Oracle CREATE SYNONYM syntax
create | = | CREATE [PUBLIC] SYNONYM (synonym_name) FOR (table_name)
### Supported COMMIT statement in all databases
commit | = | COMMIT [WORK | TRANSACTION (transaction_name)]
### Supported MySQL AUTOCOMMIT statement
autocommit | = | SET AUTOCOMMIT = (1 | 0)
### Supported Oracle hierarchical query clause The conversion of the following Oracle hierarchical query syntaxes to Common table expression (WITH clause) in DB2,SQL Server 2000 and SQL Server 2005 are supported . a. Select Query Statement with START WITH ...CONNECT BY PRIOR clause Example :: Source Oracle Query : SELECT parent1,child FROM hierrarchy START WITH parent1 = 1 CONNECT BY PRIOR parent1=child; SwisSQL Converted Query to DB2 and SQL Server 2005 : WITH ADV_RECURSIVE(parent1,child) AS (SELECT PARENT.parent1, PARENT.child FROM hierrarchy PARENT WHERE PARENT.parent1 = 1 UNION ALL SELECT CHILD.parent1,CHILD.child FROM ADV_RECURSIVE PARENT, hierrarchy CHILD WHERE CHILD.child = PARENT.parent1) SELECT parent1, child FROM ADV_RECURSIVE SwisSQL Converted Query to SQL Server 2000 : /******************* BEGIN START WITH CONNECT BY CONVERSION *********************/ CREATE FUNCTION sp_hierrarchy_hierarchy1(@current varchar(2000)) RETURNS @ctest TABLE ( PARENT1 numeric(22), CHILD numeric(22), level int, rownum int IDENTITY(1,1)) AS BEGIN DECLARE @prior varchar(2000) DECLARE @ctest1 TABLE ( PARENT1 numeric(22), CHILD numeric(22), level int) DECLARE @PARENT1 numeric(22) DECLARE @CHILD numeric(22) DECLARE @level int ; DECLARE cur CURSOR FOR select * from hierrarchy where parent1 = @current SELECT @level = 1; WHILE (1=1) BEGIN IF EXISTS(SELECT parent1 FROM hierrarchy WHERE child = @current) BEGIN SELECT @prior = @current SELECT @current = parent1 FROM hierrarchy WHERE child = @prior SELECT @level = @level + 1; INSERT INTO @ctest1 SELECT *, @level FROM hierrarchy where parent1 = @current AND child = @prior CONTINUE END ELSE BREAK END OPEN cur FETCH NEXT FROM cur INTO @PARENT1,@CHILD WHILE ( @@FETCH_STATUS = 0) BEGIN INSERT INTO @ctest VALUES(@PARENT1,@CHILD, 1) INSERT INTO @ctest SELECT * FROM @ctest1 FETCH NEXT FROM cur INTO @PARENT1,@CHILD END RETURN END GO /******************* END START WITH CONNECT BY CONVERSION ********************/ SELECT parent1, child FROM DBO.sp_hierrarchy_hierarchy1(1) ; b. Select Query Statement with START WITH ...CONNECT BY PRIOR clause and LEVEL pseudo column Example :: Source Oracle Query : select parent1,child,level from hierrarchy start with parent1=1 connect by prior parent1=child SwisSQL converted query to DB2 and SQL Server 2005 : WITH ADV_RECURSIVE(parent1,child,level) AS (SELECT PARENT.parent1, PARENT.child,1 FROM hierrarchy PARENT WHERE PARENT.parent1 = 1 UNION ALL SELECT CHILD.parent1,CHILD.child,LEVEL+1 FROM ADV_RECURSIVE PARENT, hierrarchy CHILD WHERE CHILD.child = PARENT.parent1) SELECT parent1, child, level FROM ADV_RECURSIVE SwisSQL converted query to SQL Server 2000 : /******************* BEGIN START WITH CONNECT BY CONVERSION *********************/ CREATE FUNCTION sp_hierrarchy_hierarchy1(@current varchar(2000)) RETURNS @ctest TABLE ( PARENT1 numeric(22), CHILD numeric(22), level int, rownum int IDENTITY(1,1)) AS BEGIN DECLARE @prior varchar(2000) DECLARE @ctest1 TABLE ( PARENT1 numeric(22), CHILD numeric(22), level int) DECLARE @PARENT1 numeric(22) DECLARE @CHILD numeric(22) DECLARE @level int ; DECLARE cur CURSOR FOR select * from hierrarchy where parent1 = @current SELECT @level = 1; WHILE (1=1) BEGIN IF EXISTS(SELECT parent1 FROM hierrarchy WHERE child = @current) BEGIN SELECT @prior = @current SELECT @current = parent1 FROM hierrarchy WHERE child = @prior SELECT @level = @level + 1; INSERT INTO @ctest1 SELECT *, @level FROM hierrarchy where parent1 = @current AND child = @prior CONTINUE END ELSE BREAK END OPEN cur FETCH NEXT FROM cur INTO @PARENT1,@CHILD WHILE ( @@FETCH_STATUS = 0) BEGIN INSERT INTO @ctest VALUES(@PARENT1,@CHILD, 1) INSERT INTO @ctest SELECT * FROM @ctest1 FETCH NEXT FROM cur INTO @PARENT1,@CHILD END RETURN END GO /******************* END START WITH CONNECT BY CONVERSION ********************/ SELECT parent1, child, level FROM DBO.sp_hierrarchy_hierarchy1(1) c. Select Query Statement with START WITH...CONNECT BY clause Example :: SELECT employee_id, manager_id, first_name, last_name FROM employee START WITH employee_id = 1 CONNECT BY employee_id = manager_id; ### Keywords added in 1.9.4(Union Of Keywords In All Supported Databases)
WORK | TRAN | TRANSACTION | AUTOCOMMIT
OFF | GRANT | PRIVILEGES | EXECUTE
INFILE | FIELDS | TERMINATED | BULK
COLLECT | SESSION | PUBLIC | SYNONYM
ASCII | UNICODE | BYTE | UNSIGNED
### Keywords added in 1.7(Union Of Keywords In All Supported Databases)
BITMAP | BTREE | BUFFER_POOL | CLUSTER
COMPRESS | COMPUTE | DROPEXISTING | FREELIST
FREELISTS | GROUPS | HASH | IGNORE_DUP_KEY
INITIAL | INITRANS | KEEP | LESS
MAXEXTENTS | MAXTRANS | MINEXTENTS | NEXT
NOCOMPRESS | NOPARALLEL | NOSORT | ONLINE
OPTIMAL | PAD_INDEX | PARALLEL | PCTINCREASE
RANGE | RECYCLE | REVERSE | RTREE
SORT_IN_TEMPDB | STATISTICS | STATISTICS_NORECOMPUTE | THAN
TYPE | UNLIMITED
### Keywords added in 1.6(Union Of Keywords In All Supported Databases)
ACTION | AUTO_INCREMENT | BFILE | BIGINT
BINARY | BIT | BLOB | BIGINT
BINARY | BIT | BLOB | BIGINT
CACHE | CASCADE | CHARACTER | CLOB
CLUSTERED | COMMIT | DATETIME | DBCLOB
DECIMAL | DOUBLE | FILLFACTOR | FLOAT
FORCE | FOREIGN | GLOBAL | HEAP
IDENTITY | IMAGE | INDEX | INT
IDENTITY | IMAGE | INDEX | INT
INT2 | INT4 | INT8 | INTEGER
LOCAL | LOGGING | LONG | LONGBLOB
LONGTEXT | MATCH | MATCHES | MEDIUMBLOB
MEDIUMINT | MEDIUMTEXT | MONEY | NATIONAL
NCHAR | NCLOB | NO | NOCACHE
NOLOGGING | NONCLUSTERED | NTEXT | NUM
NUMBER | NUMERIC | NVARCHAR | NVARCHAR2
ORGANIZATION | PARTIAL | PCTFREE | PCTUSED
PRECISION | PRESERVE | PRIMARY | REAL
RAW | REFERENCES | RESTRICT | TABLESPACE
SMALLDATETIME | SMALLINT | SMALLMONEY | STORAGE
STORE | TEXT | TINYBLOB | TINYINT
TINYTEXT | VARBINARY | VARCHAR | VARCHAR2
VARYING |  |  |
### Keywords Used (Union Of Keywords In All Supported Databases)
ALL | AND | ANY | AS
ASC | BETWEEN | BLOCK | BOTH
BY | CASE | CHECK | COMMA
CREATE | CONSTRAINT | CROSS | CURRENT
DATE | DEFAULT | DELAYED | DELETE
DESC | DISTINCT | DUMPFILE | ELSE
END | EXCEPT | EXEC | EXISTS
FETCH | FIRST | FOR | FROM
FULL | GROUP | GROUPING | HAVING
HIGH_PRIORITY | IGNORE | IN | INNER
INSERT | INTERSECT | INTO | IS
JOIN | KEY | LAST | LEADING
LEFT | LIKE | LIMIT | LOW_PRIORITY
MINUS | NATURAL | NOWAIT | NOT
NULL | NULLS | OF | OFFSET
ON | ONLY | OPTION | OR
ORDER | OUTER | OUTFILE | PARTITION
PERCENT | PRIOR | READ | REPLACE
RETURNING | RIGHT | ROW | ROWS
SAMPLE | SELECT | SET | SETS
SOME | SQL_BIG_RESULT | SQL_SMALL_RESULT | STRAIGHT_JOIN
SUBPARTITION | TABLE | TEMP | TEMPORARY
TIES | TIME | TIMESTAMP | THEN
TOP | TRAILING | UNION | UNIQUE
UPDATE | USING | UTE | VALUE
VALUES | WHEN | WHERE | WITH
### Supported Functions (Few functions may not have equivalent queries in all databases) ### Additionally Supported Functions in 2.0
RADIANS | DEGREES | GETUTCDATE | RAND
DB_ID | DB_NAME | SESSION_USER | HOST_ID
HOST_NAME | IS_SRVROLEMEMBER | SERVERPROPERTY | OBJECT_ID
USER_ID | UNICODE | QUOTENAME | USERENV
### Additionally Supported Functions in 1.9.4
SQUARE | ROW_NUMBER
USER_NAME | MONTHNAME
ASCII |
### Additionally Supported Functions in 1.9.2
DATENAME | RAWTOHEX | DAY | MONTH
DATEPART | YEAR | LAST_DAY | SPACE
### Additionally Supported Functions in 1.9
STD | STDV | STDDEV | VAR
VARP | VAR_POP | VAR_SAMP | VARIANCE
NEWID | BITAND
### Additionally Supported Functions in 1.8
PATINDEX | S_USER_SNAME
### Additionally Supported Functions in 1.7
DATEDIFF | SYS_GUID
### Additionally Supported Functions in 1.6
MOD | TO_DATE | TRUNC | TRUNCATE
### Additionally Supported Functions in 1.5
ADD_MONTHS | CHARINDEX | CONCATENATE | DATEADD
INITCAP | LEFT | LOCATE | NVL2
REPEAT | REPLICATE | RIGHT | STRPOS
STUFF | TO_CHAR
### Functions Supported in Earlier Releases
ABS | ACOS | ASCII | ASIN
ATAN | ATAN2 | ATN2 | AVG
CASE | CAST | CEIL | CEILING
CHAR | CHAR_LENGTH | CHARACTER_LENGTH | CHR
COALESCE | CONCAT | COS | COSH
COT | COUNT | CURRENT_DATE | CURRENT_TIME
CURRENT_TIMESTAMP | CURRENT_USER | CURRENT DATE | CURRENT TIME
CURRENT TIMESTAMP | DATALENGTH | DECODE | EXP
FLOOR | GETDATE | GROUPING | IFNULL
INSTR | ISNULL | LCASE | LEN
LENGTH | LN | LOG | LOG10
LOWER | LPAD | LTRIM | MAX
MIN | NULLIF | NVL | PI
POW | POWER | REPLACE | ROUND
RPAD | RTRIM | SIN | SINH
SQRT | SUBSTR | SUBSTRING | STR
SUM | SYSDATE | SYSTEM_USER | TAN
TANH | TRANSLATE | UCASE | UPPER
USER
### Oracle SQL to Teradata SQL Conversion: ### List of conversion features supported Conversion of: Oracle Tables, Views, and Global temporary tables to Teradata. SELECT, UPDATE, INSERT, DELETE, TRUNCATE and MERGE statements to Teradata. Oracle traditional JOIN syntax [(+)] to ANSI standard JOIN syntax supported by Teradata. Teradata reserved words used in Oracle SQL queries to quoted identifiers in Teradata. Oracle hierarchical clause to Teradata. Oracle ROWNUM clause to Teradata. Oracle DECODE statement to Teradata. Oracle partition outer join to Teradata. Oracle GROUPING_ID, WITHIN GROUP, GROUPING SET, CUBE and ROLLUP clauses to Teradata. Various string, date, data type conversion and analytic functions from Oracle to Teradata. For the list of supported functions refer to the following section below.
### List of Oracle functions converted to Teradata
LTRIM | RTRIM | LENGTH | LENGTHB | SUBSTR
SUBSTRB | TRANSLATE | CHR | TRUNC | TO_CHAR
TO_DATE | TO_NUMBER | FIRST_VALUE | LAST_VALUE | LAG
LEAD | REPLACE | RATIO_TO_REPORT | NTILE | CEIL
GREATEST | LEAST | SIGN | STDDEV | VARIANCE
POWER | INSTR | INSTR | NUMTOYMINTERVAL | NUMTODSINTERVAL
NVL2 | SYSDATE | SYSTIMESTAMP | TO_TIMESTAMP_TZ | FROM_TZ
CUME_DIST |

© 2011 ZOHO Corporation