Changes in SQLines SQL Converter tool.
In progress. Please contact us at support@sqlines.com for updates, improvements and new features.
-
-
-
-
-
-
Adding GO after every
CREATE FUNCTION statement as it must be the only statement in the batch (Informix to
SQL Server)
Released on December 08, 2024.
-
-
-
Converting
plus (+) operator to string concatenation operator (||) or
CONCAT function by default if at least one of the operands is a string (from
SQL Server, Sybase ASE, Sybase ASA)
-
-
-
-
Introducing “Conversion Complexity Score” in assessment reports
-
Converting
ROUND function to
ROUND or
TRUNC function (
SQL Server to PostgreSQL)
-
-
Converting ORDER BY clause in
STRING_AGG function (
SQL Server to PostgreSQL)
-
Converting
COUNT_BIG function to
COUNT function (
SQL Server to PostgreSQL)
Added the
-aonly command line option to generate the assessment report only without producing any
SQL output files and code snippets
-
Improving the (+) outer joins conversion to ANSI outer joins including using (+) in expressions and functions (from Oracle)
Enclosing identifiers with double quotes if they contain the number sign # character (to PostgreSQL)
Converting CONVERT(NVARCHAR(
n | MAX),
exp) to CAST(
exp AS VARCHAR(
n) | TEXT) (
SQL Server to PostgreSQL)
Converting
STDEV function to
STDDEV function (
SQL Server to PostgreSQL)
Added
-rpt=none option to skip the
HTML report file generation and
-jrpt=none option to skip the JSON report file generation
Converting
OFFSET k FETCH FIRST n by adding ORDER BY (SELECT NULL) and OFFSET 0 if required ordering and offset are not specified (IBM DB2 to
SQL Server)
Converting hexadecimal constants (literals) from
X'FF' syntax to
0xFF syntax (IBM DB2 to
SQL Server)
Fixing
ROWNUM to
LIMIT conversion (Oracle to PostgreSQL)
-
-
-
-
-
Converting
EXIT FOR statement to
BREAK statement (Informix to
SQL Server)
Released on November 04, 2024.
-
-
-
-
-
Converting
MESSAGE statement to
PRINT statement (Sybase ASA to
SQL Server)
-
Converting Transact-
SQL legacy
outer joins *= and =* to ANSI
SQL LEFT OUTER and RIGHT OUTER joins (from Sybase ASE, Sybase ASA,
SQL Server)
-
-
-
-
-
Converting
REPLICATE function to
REPEAT function (
SQL Server, Sybase ASE to PostgreSQL)
-
-
-
Converting
inserted system table reference in UPDATE statement in CREATE TRIGGER to
NEW.col, ... column references (
SQL Server to MySQL, MariaDB)
-
-
-
-
-
Released on October 07, 2024.
Applying data type mapping for derived %TYPE data types resolved using sqlines_meta.txt file (from Oracle)
Converting DECLARE
cur DYNAMIC SCROLL CURSOR to DECLARE
cur CURSOR DYNAMIC SCROLL (different keywords order) (Sybase
SQL Anywhere to
SQL Server)
Converting OPEN
cur WITH HOLD to OPEN
cur (from Sybase
SQL Anywhere)
Converting FETCH NEXT
cur to FETCH NEXT FROM
cur (Sybase
SQL Anywhere to
SQL Server)
Converting @@SQLSTATUS to @@FETCH_STATUS (Sybase
SQL Anywhere to
SQL Server)
Converting DEALLOCATE CURSOR
cur to DEALLOCATE
cur (Sybase Adaptive Server Enterprise, Sybase
SQL Anywhere to
SQL Server)
-
Converting TO_CHAR(expr) without format to CAST(expr AS CHAR) (Oracle to MySQL, MariaDB)
Converting string concatenation 'a' + 'b' + … expression to CONCAT('a', 'b', …) function (Sybase
SQL Anywhere to MySQL, MariaDB)
Parsing and removing OPAQUE TYPE storage clause in CREATE TABLE (from Oracle)
Converting 'DD.MM.YY' and 'DD.MM.YYYY' formats in TO_CHAR and TO_DATE functions (Oracle to
SQL Server)
Converting double quotes "" to `` for parameters and local variables (Sybase
SQL Anywhere to MySQL, MariaDB)
Added Expressions section to the assessment report
Converting TO_DATE('
string', 'DDMMYYYY' ) by modifying
string to DD/MM/YYYY format and using CONVERT(DATETIME,
modified_string, 103) (Oracle to
SQL Server)
Converting TO_DATE(
expr, 'DDMMYYYY' ) to CONVERT(DATETIME, STUFF(STUFF(
expr, 3, 0, '/'), 6, 0, '/'), 103) (Oracle to
SQL Server)
Added Oracle NUMBER data type used without the precision and scale (i.e. floating point number) conversion options
-ora_number and
-ora_number_int.
Converting DEFAULT AUTOINCREMENT to IDENTITY (Sybase ASA to
SQL Server)
Converting
LIST function to
STRING_AGG by default, and using STUFF with FOR
XML PATH for older versions (Sybase ASA to
SQL Server)
Converting SELECT TOP
@num to SELECT TOP (
@num) (Sybase ASA to
SQL Server)
Converting CALL
sp_name(param1, …) to EXEC
sp_name param1,… (to
SQL Server)
-
-
-
-
-
-
-
-
Released on August 12, 2024.
Escaping double quotes in JSON assessment report
Removing CREATE PACKAGE specification header except TYPE declarations (Oracle PL/
SQL to Java)
Converting TYPE
name IS RECORD to class with members, getter and setter methods (Oracle PL/
SQL to Java)
Added Conversion Issues section to the assessment report
Added subqueries used in SELECT and INSERT statements to the assessment report
-
-
Parsing ROWNUM conditions with expressions, not just integer constants (from Oracle)
Converting
JSON to CLOB for version Oracle 19 and earlier by default (to Oracle)
Extended assessment with Line metrics - need conversion, automated conversion and manual conversion at script as well as at each statement level
-
Converting
NUMBER without parameters to
DECIMAL without parameters, not to inexact DOUBLE PRECISION (Oracle to PostgreSQL)
Added details section for CREATE VIEW statements in the assessment report
Converting NOCACHE to CACHE 1 in CREATE SEQUENCE statements (Oracle to PostgreSQL)
Added aliases when converting SELECT subqueries (Oracle to MySQL, MariaDB)
Converting DELETE table to DELETE FROM table (Oracle to MySQL, MariaDB)
-
-
Creating directories specified for -log option if they do not exist
If the assessment template file sqlines_report.tpl is not found in the current directory, trying to load from the sqlines executable directory
Converting USE name to USE CATALOG name (to Databricks)
Converting CONVERT(DATE,
string) to CAST(
string AS DATE) (
SQL Server to Databricks)
Converting DATEFROMPARTS function to MAKE_DATE (
SQL Server to Databricks)
Converting EOMONTH function to LAST_DAY (
SQL Server to Databricks)
Parsing UNPIVOT clause in SELECT statement (from
SQL Server)
Improving dynamic
SQL conversion for EXEC statements (from
SQL Server)
Converting CONVERT(VARCHAR,
expr, 111) style (from
SQL Server)
Converting pattern IF EXISTS (SELECT * FROM
sysindexes …) DROP INDEX … to DROP INDEX IF EXISTS (from
SQL Server, Sybase to PostgreSQL, MySQL)
-
-
Parsing and commenting SET [TEMPORARY] OPTION statement (from Sybase
SQL Anywhere)
Converting DEFAULT TIMESTAMP to DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6) (Sybase
SQL Anywhere to MariaDB, MySQL)
Converting DEFAULT LAST USER to DEFAULT CURRENT_USER (Sybase
SQL Anywhere to MariaDB, MySQL)
Converting LONG BINARY data type to LONGBLOB (Sybase
SQL Anywhere to MariaDB, MySQL)
Converting CHAR(n > 255) data type to TEXT (to MariaDB, MySQL)
Converting IF
condition THEN
exp ELSE
exp2 ENDIF (IF expression) to IF(
condition, exp, exp2) function (Sybase
SQL Anywhere to MariaDB, MySQL)
Converting TODAY() function to CURRENT_DATE (Sybase
SQL Anywhere to MariaDB, MySQL, PostgreSQL)
Released on June 23, 2024.
Converting UPDATE
alias SET … FROM
tab alias, tab2, … WHERE … (update from another table) to UPDATE
tab alias SET … FROM
tab2, … WHERE … (
SQL Server to PostgreSQL)
Converting assignment statements var := expr to SET var = expr (Oracle to MariaDB)
Converting statement delimiter @ to ; (from IBM DB2)
Commenting CALL SYSPROC.SET_ROUTINE_OPTS('REOPT ALWAYS') system call (from IBM DB2)
Converting SET (v1, v2, …) = (SELECT c1, c2, …) to SELECT c1, c2, … INTO v1, v2, … (IBM DB2 to PostgreSQL)
Converting LOCATE(substring, string) function to POSITION(substring IN string) (IBM DB2 to PostgreSQL)
Converting CALL sp_name to PERFORM sp_name only if the stored procedure is converted to function (IBM DB2 to PostgreSQL)
Converting "ISNULL"() function (name in double quotes) to ISNULL() (Sybase ASA to
SQL Server)
Removing double quotes from built-in functions (CHAR, COUNT, LEFT, MIN, TRUNCATE and others) (Sybase ASA to
SQL Server)
Converting COMMENT ON VIEW to EXECUTE
sp_addextendedproperty (to
SQL Server)
Converting COMMENT ON PROCEDURE to EXECUTE
sp_addextendedproperty (to
SQL Server)
Fixing derived %TYPE data type mapping for CHAR, VARCHAR, VARCHAR2, TEXT and STRING to String (Oracle PL/
SQL to Java)
Parsing DROP PUBLIC SYNONYM statement (from Oracle)
Commenting DROP PUBLIC SYNONYM, CREATE PUBLIC SYNONYM and GRANT EXECUTE statements (Oracle PL/
SQL to Java)
Commenting COMMENT TO PRESERVE FORMAT ON PROCEDURE/TRIGGER statements (from Sybase ASA)
Converting procedure calls using Java naming convention for schema names (Oracle PL/
SQL to Java)
Converting FETCH INTO statement (Oracle PL/
SQL to Java)
Converting EXIT WHEN
cur%NOTFOUND to
if(!cur_found) break (Oracle PL/
SQL to Java)
Handling WHEN NO_DATA_FOUND THEN NULL exception (Oracle PL/
SQL to Java)
Creating JSON report file sqlines_report.json (defined by -jrpt option) for assessment to help build custom reports
Released on March 29, 2024.
Converting "GETDATE"(*) function to GETDATE() (Sybase ASA to
SQL Server)
Converting "DATE"(GETDATE(*)) expression to CONVERT(DATE, GETDATE()) (Sybase ASA to
SQL Server)
Converting "REPEAT"(
string, num) function to REPLICATE(
string, num) (Sybase ASA to
SQL Server)
Converting DECIMAL(65,
s) to DECIMAL(38,
s) due to data type range limitations (MySQL to
SQL Server)
Improvement for converting (+) outer joins with mixed outer, non-outer and constant conditions in WHERE clause including T.COL(+)=T.COL2(+) conditions referencing the same table (from Oracle)
Converting EOMONTH function to LAST_DAY (
SQL Server to Oracle, Redshift)
Removing constraint name for DEFAULT values (
SQL Server to MySQL, MariaDB)
Converting PRINT
text statement outside procedural block to SELECT
text AS '' (if there are expressions) or \! echo '
text' (
SQL Server to MySQL, MariaDB)
Parsing nested multi-line comments
/* comment /* nested comment */ text */ (
SQL Server, PostgreSQL etc.)
Fixing the parser for UPDATE statement with SET
col = (expr) expression in parentheses (from
SQL Server, Sybase)
Parsing UPDATE STATISTICS
table_name statement (from
SQL Server)
Escaping '\' with '\\' in string literals (
SQL Server to MySQL)
Removing (+) outer join condition if only one table is defined in the FROM clause (from Oracle)
Converting (+) outer join condition with a bind variable i.e. T1.C1(+) = :1 (from Oracle)
Fixing declaration conversion if DECLARE SQLCODE INTEGER DEFAULT 0 is specified first (IBM DB2 to PostgreSQL)
Removing DECLARE keyword from cursor declaration (IBM DB2 to PostgreSQL)
Converting OFFSET
n ROWS FETCH NEXT
k ROWS ONLY to OFFSET
n LIMIT
k (
SQL Server to PostgreSQL)
Converting SELECT
col + ',' FROM
tab FOR
XML PATH(
) to SELECT STRING_AGG(col + ',',
) FROM
tab (
SQL Server to PostgreSQL)
Moving DECLARE CURSOR statements inside procedure to the declaration block (
SQL Server to PostgreSQL)
Parsing DELETE
alias FROM
table AS
alias INNER JOIN … syntax (from
SQL Server)
Supporting table re-oredring in FROM clause when converting legacy (+) outer join syntax (from Oracle)
Parsing boolean expressions in THEN clause of CASE expression (from MySQL, PostgreSQL)
Parsing string sequence 'a' 'b' 'c' meaning single string literal 'abc' (from MySQL)
Parsing REGEXP operator (from MySQL)
Parsing CONVERT(exp USING encoding) function (from MySQL)
Parsing legacy operator && for AND condition (from MySQL)
Converting TO_CHAR(SYSDATE, 'D') to CONVERT(VARCHAR, DATEPART(DW, GETDATE())) (Oracle to
SQL Server)
Converting ending / in anonymous PL/
SQL block to GO statement (Oracle to
SQL Server)
Disclosing %ROWTYPE variables in anonymous PL/
SQL block (Oracle to
SQL Server, MySQL, MariaDB)
Parsing OBJECT_ID function with the second object type parameter (from
SQL Server)
Converting pattern IF EXISTS (SELECT * FROM sys.procedures WHERE type='P' AND name LIKE '
sproc') DROP PROCEDURE
sproc to DROP PROCEDURE IF EXISTS
sproc (
SQL Server to PostgreSQL, MySQL, MariaDB)
Released on December 29, 2023.
-
Removing IN in parameters in CREATE FUNCTION statement (Oracle to Snowflake)
Converting RETURN to RETURNS, and IS to LANGUAGE
SQL AS $$ in CREATE FUNCTION statement (Oracle to Snowflake)
Converting cursor declarations CURSOR cur(params) IS SELECT … to cur CURSOR FOR SELECT … with parameter placeholders ? (Oracle to Snowflake)
Converting OPEN cur(param1, …) to OPEN cur USING (param1, …) statement (Oracle to Snowflake)
Converting IF condition THEN … ELSIF … END IF to IF (condition) THEN … ELSEIF … END IF (Oracle to Snowflake)
Removing DETERMINISTIC option in CREATE PROCEDURE (from IBM DB2)
Extending ALTER DATABASE parser and converting COLLATE option (
SQL Server to MySQL, MariaDB)
Commenting EXEC
sp_fulltext_database (from
SQL Server)
Converting CREATE USER statement (
SQL Server to MySQL, MariaDB)
Parsing and commenting EXEC
sp_addrolemember statement (from
SQL Server)
Parsing and commenting CREATE ASSEMBLY statement (from
SQL Server)
Converting COLLATE Latin1_General_CI_AS in CREATE TABLE statement (
SQL Server to MySQL, MariaDB)
Parsing and commenting INCLUDE columns clause in CREATE INDEX statement (
SQL Server to MySQL, MariaDB)
Converting CREATE STATISTICS statement to ANALYZE TABLE statement (
SQL Server to MySQL, MariaDB)
Parsing bitwise operators | (OR) and ^ (XOR) (from
SQL Server, MySQL)
Parsing and commenting CREATE AGGREGATE statement (from
SQL Server)
Parsing and adding conversion warning for WITH XMLNAMESPACES clause in SELECT (from
SQL Server)
Parsing and adding conversion warning for SET TRANSACTION ISOLATION LEVEL SNAPSHOT (from
SQL Server)
Parsing and commenting EXECUTE
sp_addextendedproperty '
URL' (from
SQL Server)
Converting PERSISTED keyword to STORED for computed columns (
SQL Server to MySQL, MariaDB)
Removing PERSISTED keyword for computed columns (
SQL Server to Oracle)
Moving inner DECLARE statements to the beginning of BEGIN-END block (
SQL Server to MySQL, MariaDB)
Converting ALTER TABLE
tab ADD CONSTRAINT
cns DEFAULT
exp FOR
col (adding default for a column) to ALTER TABLE
tab MODIFY (
col DEFAULT
exp) (
SQL Server to Oracle)
Fixing moving expressions when converting DATEADD to INTERVAL expression (
SQL Server to Oracle)
Removing WITH CHECK clause in ALTER TABLE ADD CONSTRAINT statement (from
SQL Server)
Converting ALTER TABLE
tab CHECK CONSTRAINT
cns to ALTER TABLE
tab ENABLE CONSTRAINT
cns (
SQL Server to Oracle)
Parsing a list of options in SET statement i.e. SET ANSI_PADDING, ANSI_WARNINGS, …, ANSI_NULLS ON (from
SQL Server)
Removing IF @@ERROR <> 0 SET NOEXEC ON error handling pattern (from
SQL Server)
Converting PRINT
text statement to PROMPT
text in
SQL scripts (not procedural block) (
SQL Server to Oracle)
Fixing adding SYS_REFCURSOR for stored procedures returning result sets when existing parameters are not enclosed with () (
SQL Server to Oracle)
Fixing parser for RETURN SELECT from a table-valued function (from
SQL Server)
Converting function “NOW”() with name enclosed with double quotes (Sybase
SQL Anywhere to
SQL Server)
Using OUT for REFCURSOR when converting procedures returning result sets (IBM DB2 to PostgreSQL)
Fixing parser for single row subselect without FROM like (SELECT 1) (from
SQL Server)
Parsing DBINFO function with 2 parameters (from Informix)
Converting function CONVERT(MONEY,
exp) (from
SQL Server)
Parsing // single line comment (Snowflake)
Converting NVARCHAR(MAX) to NVARCHAR2 in parameters in functions and procedures (
SQL Server to Oracle)
Adding statement delimiter / for CREATE FUNCTION (
SQL Server to Oracle)
Moving CREATE GLOBAL TEMPORARY TABLE definitions outside CREATE PROCEDURE when converting DECLARE
@name TABLE local tables (
SQL Server to Oracle)
Converting FROM
table_func(params) to FROM TABLE(
table_func(params)) for calling table valued functions (
SQL Server to Oracle)
Converting DECLARE
@var type = (SELECT …) to a standalone declaration and SELECT INTO statement (
SQL Server to Oracle)
Converting UNIQUEIDENTIFIER to CHAR in function and procedure parameters (
SQL Server to Oracle)
Handling reserved words in identifiers (to Oracle)
Parsing COUNT(UNIQUE col1, …) function (from Informix)
Converting SELECT … FROM … LIMIT
n to SELECT TOP
n … clause (MySQL to
SQL Server)
Released on December 04, 2023.
Converting TO_CHAR(
exp, 'YYYY/MM/DD HH24:MI:SS') to REPLACE(CONVERT(VARCHAR(19),
exp, 20), '-', '/') (Oracle to
SQL Server)
Converting IIF function to CASE expression (
SQL Server to Synapse)
Converting DATEDIFF(YEAR,
dt1, dt2) to EXTRACT(YEAR FROM AGE(
dt2, dt1)) (
SQL Server to PostgreSQL)
Commenting ALTER TABLE
table CHECK CONSTRAINT
constraint (
SQL Server to MariaDB)
Commenting CONNECT TO statement (from IBM DB2)
Converting CREATE SEQUENCE statement (IBM DB2 to Snowflake)
Converting string1 CONCAT string2 operator to || operator (IBM DB2 to PostgreSQL, Redshift)
Converting VALUES c1, c2, … INTO v1, v2, … to individual v1 := c1; v2 := c2; … assignment statements (from IBM DB2)
Commenting FULLTEXT INDEX in CREATE TABLE (from MySQL, MariaDB)
Converting ENUM data type to VARCHAR with CHECK constraint (from MySQL, MariaDB)
Converting SET data type to VARCHAR (from MySQL, MariaDB)
Commenting ON UPDATE CASCADE clause in FOREIGN KEY constraint (MySQL, MariaDB to Oracle)
Adding CHAR length semantics specifier explicitly when converting CHAR(n) and VARCHAR(n) data types (MySQL, MariaDB to Oracle)
Converting VARCHAR(n) BINARY to RAW(n) (MySQL to Oracle)
Converting stored procedures with CURSOR WITH RETURN (result sets) (IBM DB2 to PostgreSQL)
Removing ON ROLLBACK DELETE ROWS in temporary tables (IBM DB2 to PostgreSQL)
Converting VARCHAR(exp, length) function to CAST(exp AS VARCHAR(length)) (from IBM DB2)
Converting TIMESTAMP function (from IBM DB2)
Bug fix - ALTER TABLE tab ALTER COLUMN col RESTART WITH num conversion for PostgreSQL 10 and later (IBM DB2 to PostgreSQL)
Bug fix -
string+LEFT/RIGHT() expression conversion (
SQL Server to PostgreSQL)
Bug fix - DAY function conversion with string literal, adding DATE before the literal (
SQL Server to PostgreSQL)
Bug fix - When converting DATEDIFF enclose expression with () before casting to TIMESTAMP like
f() + … to (
f() || … )::TIMESTAMP (
SQL Server to PostgreSQL)
Bug fix - Parsing ON UPDATE CURRENT_TIMESTAMP() in CREATE TABLE (from MySQL, MariaDB)
Bug fix - Converting CURRENT_TIMESTAMP() to CURRENT_TIMESTAMP (from MySQL, MariaDB to Oracle)
Bug fix - Parsing storage options for CREATE TABLE (ENGINE, AUTO_INCREMENT, CHARSET etc.) (from MariaDB)
Bug fix - BIT data type conversion (MariaDB to Oracle)
Bug fix - CREATE PROCEDURE parser when there are no parameters (from IBM DB2)
Bug fix - Fixing the parser when UNION keyword is used for an alias (from IBM DB2)
Bug fix - Removing WITH HOLD clause from cursor declarations (from IBM DB2)
Released on September 10, 2023.
Converting
LISTAGG function to STRING_AGG function supporting ORDER BY conversion (Oracle to PostgreSQL)
Converting
FROM_TZ function to AT TIME ZONE expression (Oracle to
SQL Server)
AT TIME ZONE timezone name conversion support i.e. 'EST' to 'Eastern Standard Time' (Oracle, PostgreSQL to
SQL Server)
Removing 'NLS_DATE_LANGUAGE = language' when converting TO_DATE and TO_CHAR functions (from Oracle)
Converting TRUNC(
exp, 'MI') to CONVERT(DATETIME, CONVERT(VARCHAR(16),
exp, 120) + ':00') (from Oracle to
SQL Server)
Converting TRUNC(
exp, 'MONTH') and TRUNC(
exp, 'MON') to CONVERT(DATETIME, CONVERT(VARCHAR(7),
exp, 120) + '-01') (from Oracle to
SQL Server)
Converting EXTRACT(HOUR FROM
datetime) function to DATEPART(HOUR,
datetime) (Oracle to
SQL Server)
Converting EXTRACT(MINUTE FROM
datetime) function to DATEPART(MINUTE,
datetime) (Oracle to
SQL Server)
Converting TO_CHAR(
exp, 'MON-YYYY') to REPLACE(RIGHT(CONVERT(VARCHAR(11),
exp, 106), 8), ' ', '-') (Oracle to
SQL Server)
Converting TO_CHAR(
exp, 'MM/DD/YYYY') to CONVERT(VARCHAR(10),
exp, 101) (Oracle to
SQL Server)
Converting INSERT
table statement to INSERT INTO
table statement (
SQL Server, MySQL to Oracle, IBM DB2)
Converting positional GROUP BY 1, 2, 3 to GROUP BY with SELECT expressions (to
SQL Server)
Converting specific
OUTER join to ANSI
SQL LEFT OUTER JOIN (from Informix)
Converting PRIMARY KEY (c1, c2, …) CONSTRAINT schema.name to CONSTRAINT name PRIMARY KEY (c1, c2, …) (from Informix)
Bitwise BITAND function to & (AND) operator conversion (Oracle to
SQL Server)
Removing NO ORDER clause from GENERATED AS IDENTITY (from IBM DB2)
Removing ORGANIZE BY ROW clause in CREATE TABLE (from IBM DB2)
Removing INCLUDE NULL KEYS from CREATE INDEX options (from IBM DB2)
Removing the schema name when converting CREATE INDEX statement (to PostgreSQL)
Bug fix - Parsing 'NLS_DATE_LANGUAGE = language' in TO_CHAR function (from Oracle)
Bug fix - Converting TO_TIMESTAMP(
string, 'YYYY-MM-DD HH24:MI:SS.FF3') to CONVERT(DATETIME,
string) (Oracle to
SQL Server)
Bug fix - Converting TO_DATE(
string, 'MM/DD/YYYY') to CONVERT(DATETIME,
string) (Oracle to
SQL Server)
Bug fix - CROSS JOIN clause parser
Bug fix - Convering AFTER UPDATE, INSERT, DELETE events to AFTER UPDATE OR INSERT OR DELETE in triggers (
SQL Server to PostgreSQL)
Bug fix - Moving ON
table AFTER INSERT after the trigger event AFTER INSERT ON
table in CREATE TREIGGER conversion (
SQL Server to PostgreSQL)
Bug fix - Adding FOR EACH ROW when converting triggers (
SQL Server to PostgreSQL)
Released on July 25, 2023.
Converting JSON_VALUE function to JSONB_PATH_QUERY function (Oracle to PostgreSQL)
Using TRIM available since
SQL Server 2017 by default and convert to RTRIM(LTRIM(
string) is version is lower than 2017 (to
SQL Server)
Converting TRUNC(datetime) to DATE_TRUNC('day', datetime) (Oracle to PostgreSQL)
Parsing CONNECT_BY_ROOT and CONNECT_BY_ISLEAF hierarchical query functions (from Oracle)
Conversion errors section conversion_errors in the assessment report template sqlines_report.tpl
Conversion warnings section conversion_warnings in the assessment report template sqlines_report.tpl
Converting STRING data type to TEXT (Hive to PostgreSQL)
Parsing STORED AS type clause (from Hive)
Converting FROM_UNIXTIME(epoch) function to TO_TIMESTAMP(epoch) (Hive, Spark to Snowflake)
Converting LIMIT offset, rows to OFFSET offset ROWS FETCH NEXT rows ROWS ONLY (MySQL to Oracle)
Parsing UPDATE t INNER JOIN t2 ON … SET … WHERE statement (from MySQL)
Converting UPDATE t INNER JOIN … statement to UPDATE FROM (MySQL to PostgreSQL)
Converting MERGE to MERGE INTO statement (
SQL Server to PostgreSQL)
Converting
DATEPART function to DATE_PART (
SQL Server to PostgreSQL)
Bug fix - Not adding an alias for FROM (t1 JOIN t2 …) clauses (Oracle to
SQL Server, PostgreSQL)
Bug fix - Redundant ( could be added when converting MONTHS_BETWEEN to DATEDIFF(MONTH, …) when
-udf_months_between=no is set (Oracle to
SQL Server)
Bug fix - Converting SYSDATE to CURRENT_TIMESTAMP(0), was CURRENT_TIMESTAMP (Oracle to PostgreSQL)
Bug fix - Converting CONNECT BY PRIOR with preceeding UNION ALL clause (from Oracle)
Bug fix - Converting DECODE with NULL condition to searched CASE expression (from Oracle)
Bug fix - SELECT statements in SELECT expression list of other SELECT statements were reported as standalone SELECT statements (Assessment Report)
Bug fix - Converting session variables @var to local variables v_var when there are no any declaration in source procedure (from MySQL)
Bug fix - Parsing qualified identifiers having spaces around dot (.)
Bug fix - Determing object type (procedure or function) when converting DENY EXECUTE to REVOKE EXECUTE ON PROCEDURE | FUNCTION (
SQL Server to PostgreSQL)
Bug fix - DATEADD to INTERVAL expression conversion if an expression is added (not a single number) (
SQL Server to PostgreSQL)
Bug fix - DATEADD to INTERVAL expression conversion for
ss and
mi units (
SQL Server to PostgreSQL)
Released on July 09, 2023.
Converting
JSON_TABLE function (Oracle to PostgreSQL)
Converting
PIVOT clause to CASE expressions with aggregations (
SQL Server to PostgreSQL)
Converting
CONNECT BY with multiple tables to recursive CTE (from Oracle)
Converting EXCEPTION WHEN NO_DATA_FOUND to IF @@FETCH_STATUS <> 0 BEGIN … END block (Oracle to
SQL Server)
Parsing CREATE FUNCTION with optional BEGIN-END keywords (from Sybase ASE)
Converting AUTO_INCREMENT to GENERATED ALWAYS AS IDENTITY by default (MySQL to PostgreSQL)
Convering CALL sp_name from another stored procedure to PERFORM sp_name (MySQL to PostgreSQL)
Converting session variables @var to local variables if they are initialized within the stored procedure (MySQL to PostgreSQL)
Removing alias when converting simple form of UPDATE
tab alias SET
alias.col… WHERE
alias.col… (Oracle to
SQL Server)
Removing NO ALLOW REVERSE SCANS from CREATE INDEX (from IBM DB2)
Converting SET CURRENT SCHEMA = name to SET SCHEMA 'name' (IBM DB2 to PostgreSQL)
Converting SET CURRENT PATH to SET search_path TO (IBM DB2 to PostgreSQL)
Converting SIGNAL SQLSTATE 'sqlstate' ('message') to RAISE EXCEPTION SQLSTATE 'sqlstate' USING MESSAGE = 'message' (IBM DB2 to PostgreSQL)
Removing NO CASCADE option in CREATE TRIGGER (from IBM DB2)
Converting PRINT statement to RAISE NOTICE (
SQL Server to PostgreSQL)
Converting EXEC sp_executesql to EXECUTE USING (
SQL Server to PostgreSQL)
Converting BREAK statement to exit from a loop to EXIT (
SQL Server to Oracle, PostgreSQL)
Using smaller number of REFCURSOR when converting result sets in IF ELSE branches (
SQL Server to PostgreSQL)
-column_name_quotes option to quote column identifiers
-object_name_quotes option to quote object name identifiers (schemas, tables, views, aliases etc.)
-object_name_case option to convert object names (tables i.e.) to upper or lower case in DDL scripts
-column_name_case option to convert column names to upper or lower case in DDL scripts
-pg_use_function_single_result_set option to convert result sets returned from stored procedure to RETURN QUERY (
SQL Server, Sybase, MySQL to PostgreSQL)
Parsing PRINT statement with format and parameters (from Sybase)
Parsing READ_ONLY option in DECLARE CURSOR (from
SQL Server)
Converting GETUTCDATE() to CURRENT_TIMESTAMP(3) AT TIME ZONE 'UTC' (
SQL Server to PostgreSQL)
Converting DENY EXECUTE ON
name TO
role to REVOKE EXECUTE ON PROCEDURE
name FROM
role (
SQL Server to PostgreSQL)
Parsing PIVOT clause (from
SQL Server)
Bug fix - SELECT … INTO
var conversion to SELECT
@var = … (wrong location of variable) (Oracle to
SQL Server)
Bug fix - Adding (cur REFCURSOR) when a stored procedure has empty parameter list () (MySQL to PostgreSQL)
Bug fix - Adding END at the end of trigger block to single statement triggers (to PostgreSQL)
Bug fix - Adding () around parameters when converting EXECUTE
sp_name @parm = @value with single parameter (
SQL Server to PostgreSQL)
Bug fix - Parsing SELECT
@var = (…) for non-SELECT assignment expressions starting with open parenthesis (from
SQL Server, Sybase)
Bug fix - Outer join (+) conversion for 3 and more table when condition goes right after WHERE clause (from Oracle)
Released on June 26, 2023.
Converting
CASE statement to IF statement (from IBM DB2, Oracle to
SQL Server)
Converting
HEX function to CONVERT expressions (from IBM DB2 to
SQL Server)
Converting
TRANSLATE function changing the order of parameters and dealing with short replacement string (IBM DB2 to
SQL Server)
Converting
LISTAGG function STRING_AGG (Oracle to
SQL Server)
Converting CHAR(
string, num) function to CAST(
string AS CHAR(
num)) (IBM DB2 to
SQL Server)
Converting ROUND(
num) with single parameter to ROUND(
num, 0) (to
SQL Server)
Converting EXTRACT(SECOND FROM
datetime) to DATEPART(SECOND,
datetime) (Oracle to
SQL Server)
Converting TO_CHAR(expr) without format to expr::text (Oracle to PostgreSQL)
Converting
STR function to TO_CHAR with the corresponding format (
SQL Server to Oracle, PostgreSQL)
Converting NOCACHE, NOORDER, NOCYCLE and MAXVALUE options of
CREATE SEQUENCE statement (Oracle to
SQL Server)
Removing () when converting INSERT … (SELECT …) (from Oracle to
SQL Server)
Parsing DENY EXECUTE ON statement (from
SQL Server)
Bug fix - Converting parameters to
@param if they were defined as
“param” but later referenced as
param (to
SQL Server)
Bug fix - Parsing SELECT
@ret = exp FROM … assignment (from Sybase
SQL Anywhere)
Bug fix - Parsing legacy outer join operators *= and =* in WHERE clause (from Sybase
SQL Anywhere)
Bug fix - Parsing ORDER BY clause in XMLAGG function (from IBM DB2, Oracle)
Bug fix - Parsing INHERIT ISOLATION LEVEL WITH | WITHOUT LOCK REQUEST clause in CREATE FUNCTION (from IBM DB2)
Bug fix - Parsing CONVERT(FLOAT,
expr) function (from
SQL Server)
Bug fix - Parsing CONVERT(DECIMAL(
p,s,
expr) function (from
SQL Server)
Bug fix - Converting CONVERT(DATETIME,
expr) to CAST(
expr AS TIMESTAMP(3)) (
SQL Server to PostgreSQL)
Bug fix - Converting CONVERT(BIT,
expr) to CAST(
expr AS BOOLEAN) (
SQL Server to PostgreSQL)
Bug fix - Redandant END IF was added for IF … ELSE IF … conversion (
SQL Server to PostgreSQL)
Released on May 29, 2023.
Converting STUFF function with SELECT … FOR
XML PATH to GROUP_CONCAT (
SQL Server to MariaDB, MySQL) and LISTAGG (
SQL Server to Oracle)
Converting BEGIN CATCH to DECLARE CONTINUE HANDLER FOR SQLEXCEPTION (
SQL Server to MariaDB, MySQL)
Remove options NOKEEP, NOSCALE, GLOBAL from CREATE SEQUENCE (Oracle to PostgreSQL)
Converting sequence options for GENERATED AS IDENTITY in CREATE TABLE statement (Oracle to PostgreSQL)
Converting conditional predicate UPDATING to TG_OP = 'UPDATE' in CREATE TRIGGER (Oracle to PostgreSQL)
Converting REFERENCING NEW AS … OLD AS … to REFERENCING NEW TABLE AS … OLD TABLE AS … (Oracle to PostgreSQL)
Not adding
p_ parameter prefix when converting parameters starting from
@p_ (from
SQL Server, Sybase)
Parsing standalone hint specified without WITH keword i.e.
SELECT * FROM orders (NOLOCK) (from
SQL Server)
Converting date and time literals TIMESTAMP '2023-05-23 09:30:00' to { ts '2023-05-23 09:30:00' } (Oracle to
SQL Server)
Removing OR REPLACE and terminating / when converting CREATE OR REPLACE TYPE statement (Oracle to PostgreSQL)
-udf_months_between option to define whether to use UDF to implement Oracle MONTHS_BETWEEN function in
SQL Server or simplified conversion to DATEDIFF(MONTH, …) that produces different result (Oracle to
SQL Server)
Removing WITH READ ONLY clause from CREATE VIEW (Oracle to MariaDB Oracle compatibility mode)
Bug fix - Removing constraint name for inline named NOT NULL constraints in CREATE TABLE (Oracle to MariaDB Oracle compatibility mode)
Bug fix - Oracle (+) outer join conversion when the order of tables/columns in FROM and WHERE clauses is different and LEFT/RIGHT join type was not correct (from Oracle)
Bug fix - Changing column reference in trigger for redefined NEW i.e. from :NEWROW.c1 to NEWROW.c1 (Oracle to PostgreSQL)
Bug fix - Scanning sub-directories for searching files specified by -in option
Bug fix - Removing MAX when converting CONVERT(NVARCHAR(MAX),
string) to CONVERT(
string, CHAR) (from
SQL Server to MariaDB, MySQL)
Bug fix - Converting CURRENT_DATE that includes time part (Oracle to
SQL Server)
Bug fix - Convertng TRUNC(CURRENT_DATE) expression (Oracle to
SQL Server)
Bug fix - Converting TRUNC(
number) to FLOOR (Oracle to
SQL Server)
Bug fix - Fixing IF BEGIN … END; ELSE IF … parser when END followed by optional semicolon (;) (from
SQL Server)
Bug fix - Replacing PL/
SQL delimiter / with // and resetting DELIMITER ; in CREATE PROCEDURE (Oracle to MariaDB Compatibility mode)
Bug fix - Fixing error “Error writing output” when converting a single file and -out specifies the existing directory
Released on March 22, 2023.
Generating function that RETURNS TRIGGER when converting CREATE TRIGGER (Oracle to PostgreSQL)
Converting RAISE_APPLICATION_ERROR to RAISE EXCEPTION (Oracle to PostgreSQL)
Converting GETDATE() + 1.1 (decimal expressions) to INTERVAL expressions (
SQL Server to MySQL, MariaDB, SingleStore)
Converting SELECT statements returning result sets in stored procedures to DECLARE CURSOR WITH RETURN and OPEN cursor statements (
SQL Server, Sybase, MySQL to DB2)
Recognizing pattern IF EXISTS (SELECT * FROM sysobjects WHERE … OBJECTPROPERTY(id, N'ISPROCEDURE') = 1) DROP PROCEDURE (from
SQL Server)
Recognizing pattern IF EXISTS (SELECT * FROM sysobjects WHERE … OBJECTPROPERTY(id, N'ISVIEW') = 1) DROP VIEW (from
SQL Server)
Adding explicit casting when converting '0' < 1 string-number comparison (Oracle to PostgreSQL)
Adding Azure Synapse as target database
Adding code snippets for non-
ASCII identifiers in the assessment report
Converting RPAD function (Oracle, MySQL, PostgreSQL, Netezza to
SQL Server, Synapse)
Converting PRIMARY KEY to PRIMARY KEY NONCLUSTERED NOT ENFORCED (to Synapse)
Converting UNIQUE constraint to UNIQUE NOT ENFORCED (to Synapse)
Removing schema name in DROP TRIGGER and CREATE TRIGGER statements (to PostgreSQL)
Adding table name to DROP TRIGGER (to PostgreSQL)
Converting SQLCODE to SQLSTATE (Oracle to PostgreSQL)
Converting conditional predicate INSERTING to TG_OP = 'INSERT' in CREATE TRIGGER (Oracle to PostgreSQL)
Converting DATETIME2(
p) to TIMESTAMP(
p) (
SQL Server to Snowflake)
Parsing and commenting ORGANIZATION EXTERNAL clause in CREATE TABLE (from Oracle)
Commenting GLOBAL TEMPORARY in CREATE TABLE (to
SQL Server)
Moving NOT NULL constraint from ALTER TABLE … MODIFY (
column NOT NULL ENABLE) to CREATE TABLE statement (Oracle to
SQL Server)
Renaming primary constraint name if it is the same as an index name defined on the table (to
SQL Server)
Changing the data type of the foreign key column to match the data type of the primary key column (to
SQL Server)
Adding NOT NULL constraints explicitly for primary key columns (Oracle to
SQL Server)
Bug fix - Parsing declaration block in CREATE TRIGGER (Oracle to PostgreSQL)
Bug fix - Not specifying INTERVAL DAY literal precision when used in PL/
SQL code (to Oracle)
Bug fix - Converting DATEADD(HH,
datetime) function (
SQL Server, Sybase to Oracle, PostgreSQL)
Bug fix - Removing function name after END function if CREATE FUNCTION has the qualified name schema.function (Oracle to PostgreSQL)
Bug fix - Handling UTF-8 0xC2A0 sequence (non-breaking space) in the source scripts as it broke the
SQL parser
Bug fix - Converting :NEW.column and :OLD.column references to NEW.column and OLD.column in CREATE TRIGGER (Oracle to PostgreSQL)
Bug fix - Adding PERFORM for procedure call within a trigger (Oracle to PostgreSQL)
Bug fix - Converting enclosed [
schema]] name (
SQL Server to Snowflake)
Bug fix - Removing schema name from index when the schema name mapping is applied (to
SQL Server, MariaDB, MySQL)
Bug fix - Parsing USING INDEX for primary key columns (from Oracle)
Released on February 15, 2023.
Dynamic
SQL conversion referenced in EXEC statements (from
SQL Server, Sybase)
Moving in-place declarations to DECLARE section of BEGIN-END block (to Oracle)
Converting BEGIN CATCH to EXCEPTION WHEN OTHERS block (
SQL Server to SingleStore)
Converting RAISERROR function to RAISE USER_EXCEPTION (
SQL Server to SingleStore)
Convert FORMAT function to DATE_FORMAT (
SQL Server to MySQL, MariaDB, SingleStore)
Commenting SET XACT_ABORT ON statement in stored procedures (
SQL Server to Singlestore)
Commenting SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED | COMMITTED in stored procedures (
SQL Server to Singlestore)
Converting EXEC
sp_name params procedure call into
sp_name(params) (
SQL Server, Sybase to Oracle)
Parsing FORWARD_ONLY and STATIC cursor options in DECLARE CURSOR statement (from
SQL Server)
Converting pattern IF EXISTS (SELECT * FROM sys.objects … ) DROP TRIGGER (from
SQL Server)
Converting patterns IF EXISTS (SELECT * FROM sysobjects … ) DROP … (from
SQL Server)
Converting
SQL*Plus EXECUTE to CALL statement with named parameters (Oracle to PostgreSQL)
Parsing ALTER TABLE ADD column for adding multiple columns in single statement (from Oracle)
Parsing FOREIGN KEY
name (
columns) syntax (from DB2 z/
OS)
Allowing to define empty prefix for -tsql_parameters_prefix option by specifying ”” or '' value
Commenting COLLATE
name clause for expressions (from
SQL Server)
Bug fix - Not adding FROM
dual when converting SELECT
@var=expr to
v_var := expr (
SQL Server, Sybase to Oracle)
Bug fix - Removing constraint name for inline named NOT NULL constraints in CREATE TABLE (Oracle to MySQL)
Bug fix - Converting DECLARE
@tab AS TABLE to CREATE GLOBAL TEMPORARY TABLE (
SQL Server to Oracle)
Bug fix - Replace comma (,) with semicolon (;) when converting SELECT
@var = exp, @var2 = exp2, … to
var := exp; @var2 = exp2 (
SQL Server, Sybase to Oracle, PostgreSQL)
Bug fix - Parsing CROSS APPLY join when AS keyword is not specified before the alias (from any database supporting CROSS APPLY)
Bug fix - DATEADD(
dd, num, exp) conversion - replace DD with DAY in interval expression (
SQL Server, Sybase to Oracle)
Bug fix - Parsing ELSE clause when converting IF @@ERROR <> 0 to EXCEPTION block (
SQL Server to Oracle)
Bug fix - Removing DEALLOCATE cursor statement if semicolon (;) follows CLOSE
cursor (from
SQL Server)
Bug fix - Parsing WITH (NOLOCK) for multi-table FROM without table aliases (from
SQL Server)
Bug fix - Converting
#tmp.column identifiers referencing temporary tables (from
SQL Server, Sybase)
Bug fix - Converting enclosed table alias
[alias] (from
SQL Server, Sybase)
Bug fix - Adding semicolon (;) when converting END CATCH to END; (
SQL Server to SingleStore)
Bug fix - Converting enclosed identifiers for temporary tables
[#tmp] (from
SQL Server)
Bug fix - Parsing SELECT * INTO
table FROM
table2 for non-temporary tables (from
SQL Server, Sybase)
Released on February 07, 2023.
-ident option to transform the identifiers using the specified template
Converting INSTR function with 4 parameters to CHARINDEX if the 4th parameter has value of 1 (i.e. to find the first occurrence) (Oracle to
SQL Server)
Outer join syntax (+) conversion for more than 2 tables (from Oracle)
Data type mapping (sqlines_dtmap.txt) for various combinations of length and scale for numeric data types
Reporting syntax errors with code snippets in sqlines_report.html file when -a assessment option is specified
Adding OR REPLACE when converting CREATE PROCEDURE statement (to SingleStore)
Removing IN, commenting OUT parameter specifiers for stored procedures (MySQL to SingleStore)
Removing LANGUAGE
SQL, NOT DETERMINISTIC, READS
SQL DATA and COMMENT clauses when converting stored procedures and user-defined functions (from MySQL)
Moving declarations before BEGIN-END block (MySQL to SingleStore)
Converting USE
db_name to SET SCHEMA
name (
SQL Server to DB2)
Removing GO statement from CREATE PROCEDURE (
SQL Server to DB2)
Converting IDENTITY property to GENERATED ALWAYS AS IDENTITY (
SQL Server, Sybase to DB2)
Removing ASC, DESC column sorting from PRIMARY KEY constraints (
SQL Server to DB2)
Parsing SELECT … FROM … WHERE … INTO variables clause (INTO at the end of statement) (from MySQL)
Converting SELECT statements returning result sets to ECHO SELECT (
SQL Server, MySQL to SingleStore)
Released on January 31, 2023.
-remd option to remove database name in 3-part identifiers (from
SQL Server, Sybase)
Commenting foreign key constraints in CREATE TABLE (to SingleStore)
Converting _utf8mb4'string' string literals to _utf8'string' (MySQL to SingleStore)
COMMENT ON TABLE conversion (to
SQL Server)
Bug fix:
INSTR to dbo.INSTR4 UDF conversion (Oracle to
SQL Server)
Bug fix: FOR i IN k..m range loop (from Oracle)
Bug fix:
-smap schema mapping option
Bug fix: ALTER TABLE
name ADD
col type - removing GO (from
SQL Server)
Bitwise & (AND) operator to BITAND function (
SQL Server to Oracle)
Bug fix: DROP FUNCTION statement parser
IF EXISTS (SELECT * FROM sys.objects WHERE type = 'FN' …) DROP FUNCTION pattern recognition (from
SQL Server)
Bug fix: NVARCHAR(MAX) to NCLOB conversion (
SQL Server to Oracle)
Bug fix: Removing size for NVARCHAR(n) in parameters for procedures and functions (to Oracle)
Bug fix: Parser CREATE FUNCTION … RETURNS @tab TABLE (cols) column definitions (from
SQL Server)
Removing WITH ENCRYPTION, SCHEMABINDING options in CREATE FUNCTION (from
SQL Server)
Bug fix: Parser rules fix - OPEN keyword cannot be an column alias (from
SQL Server)
Removing OPTION (MAXRECURSION
num) query hint (from
SQL Server)
Parsing ENUM data type (from MySQL)
Parsing SET data type (from MySQL)
Parsing FULLTEXT index in CREATE TABLE (from MySQL)