Unsupported SQL Syntax |

Parsing and Conversion Limitations
### Parsing and Conversion Limitations Conversion of GROUP BY extensions (ROLLUP, CUBE, and GROUPING SETS) with multiple braces (incase of Oracle and DB2) are not supported. Example: select * from emp group by ROLLUP(province,(country,city)); select * from emp group by CUBE(province,(country,city)); select week(sales_date) as week, daysofweek(sales_date) as day_week, sales_person, sum(sales) as units_sold from sales where week(sales_date) = 13 group by grouping sets(rollup(week(sales_date),dayofweek(sales_date)),rollup(month(sales_date),region)) order by week, day_week, sales_person; In ANSI Outer Join syntax, the columns that are joined in the query must be prefixed by table or alias name. Joins with only column names may be converted incorrectly. Future releases will use meta data information to support this feature. Example: select * from j1 inner join j2 on w = y; select * from emp inner join employee on id=emp_id and name = 'vembu'; Braces not supported after 'FROM' except in subqueries Example: select r1,r2 from (values('group1','group2')) as x(r1,r2); Braces are not supported in some rare cases in 'where' clause Example: select * from emp,emp1 where (emp.id(+))=emp1.id; Conversion of 'ESCAPE' clause is not supported in all databases Example: select id from emp where emp.id like '\%\_\\%' escape '\"'; Optimization Issues are not supported Example: select (id) from (select id from emp where name like 's%' with read only); select (id) from (select id from emp1 with read only); select (id) from (select id from emp1 with check option); select (id) from (select id from emp1 with check option constraint c1); select top 10 percent with ties * from authors order by author_id; select * from table (select * from emp) as alias_table order by id for read only; select * from table (select * from emp) as alias_table optimize for 10 rows; Conversion of Oracle's 'SAMPLE' clause is not supported Example: select id from emp sample block(10) where id >1; Oracle NESTED TABLES are not supported Example: select nt.name from the(select employee1 from top) nt; 'XML' features are partially supported Example: select a.author_id tag,avg(a.author_id) parent,max(a.name) tag!12!tagAtt from authors a,title_author b where a.author_id=b.author_id group by a.author_id,b.author_id for xml explicit; Informix Subscripts on Character Columns is not supported Example: SELECT Firstname, Lastname, Company FROM customers WHERE city[1,3] = 'New' SubQueries are not converted to MySQL from any database, since SubQueries are not available in MySQL. Example: select * from (select * from (select * from emp), emp1); This will not be converted to mysql Informix OUTER joins are only partially supported Example: SELECT * FROM OUTER(EMP A ,EMP2 B) , EMP1 C WHERE A.ID = B.ID AND B.NAME = C.NAME Conversion of Oracle Hierarchical Clause 'START WITH ...CONNECT BY' is not supported for databases other than DB2 and SQL Server . Conversion of Oracle HINTS is not supported Conversion of SQL Server QUERY HINTS is not supported Indexes, CHECK constraint, CUBE clause of GROUP BY clause, Ref columns and Ref constraints are not converted to Netezza. Materialized view of mutiple tables are not converted to Netezza. Recursive common table expressions (WITH statement) are not converted into derived tables, only normal common table expressions and nested common table expressions are converted.

© 2011 ZOHO Corporation