Views and Queries translation between different databases.

All modern database systems like MS SQL Server, Oracle, MySQL, PostgreSQL, MS Access use similar SQL syntax but that does not mean they may be transferred “as is” between databases. Every database uses its own SQL dialect and SQL statements have to be translated at first in order to be readable by target database.

You don’t have to be an SQL guru and it does not require any SQL knowledge from your side to translate Views/ Queries between different database engines. Our solutions perform Automatic Views and Queries conversion between MS Access, MS SQL Server, MySQL, Oracle and PostgreSQL.

Our innovative parser analyses SQL Statements in source database and builds appropriate analogs for target db accordingly.

Just check queries/ views need to be translated in the list of views at customization stage. The rest will be done for you automatically.

The following view types may be translated automatically using our programs:

  • SELECT type Queries/ Views;
  • Queries with standard functions (not including statistical with NZ prefix for MS Access)
  • SelectQuery and unionQuery for MS Access in case they have no input parameters.
  • Views with UNION, SUBQUERYS, CAST, CONVERT, EXIST statements for MySQL and MS SQL.

Views conversion support is available in the following converters in two directions:

Queries to views conversion is available only in one direction:

Below you can find examples show pairs of views/ queries translated with our software.

MS SQL Server <=> MySQL

SELECT F_T_1.ID, A_1.Dsc,
(SELECT ELat FROM dbo.A AS A_2 WHERE (ELat = 'Row')) AS Expr1
FROM dbo.F_T AS F_T_1
RIGHT OUTER JOIN dbo.A AS A_1 ON F_T_1.ID = 
A_1.ID AND F_T_1.Tipo = A_1.Dsc
UNION
SELECT F_T_1.ID, A_1.Dsc, '#' FROM dbo.F_T
WHERE (F_T_1.Data > CONVERT (DATETIME, '2007-02-14 00:00:00'))
            
SELECT 'F_T_1'.'ID' AS 'ID', 'A_1'.'Dsc' AS Dsc,
(SELECT 'A_2'.'ELat' AS 'ELat' FROM 'a' 'A_2'
WHERE ('A_2'.ELat = 'Row')) AS Expr1
FROM 
('a' 'A_1' LEFT JOIN 'f_t' 'F_T_1' ON ((('F_T_1'.'ID' = 'A_1'.'ID')
AND ('F_T_1'.'Tipo' = 'A_1'.'Dsc'))))
UNION
SELECT 'F_T_1'.'ID' AS 'ID', 'A_1'.'Dsc' AS 'Dsc', '#' FROM 'f_t-1'
WHERE ('F_T_1'.'Data' > CAST ('2007-02-14 00:00:00' AS datetime))
            

MS SQL Server <=> Oracle

SELECT TOP (100) PERCENT dbo.dossiers.external_authorization,
dbo.dossiers.timestamp, dossiers_1.id
FROM dbo.dossiers 
INNER JOIN
dbo.GiornalePorto ON dbo.dossiers.id_nave = dbo.GiornalePorto.ID
INNER JOIN
dbo.dossiers AS dossiers_1 
ON dbo.GiornalePorto.ID = dossiers_1.id
WHERE (dbo.GiornalePorto.ID >= RAND(1))
ORDER BY dbo.GiornalePorto.DataOraConcordata
SELECT "dossiers" . "external_authorization",
"dossiers" . "timestamp", "dossiers_1" . "id"
FROM "dossiers" 
INNER JOIN 
"GiornalePorto" ON "dossiers" . "id_nave" = "GiornalePorto" . "ID"
INNER JOIN 
"dossiers" "dossiers_1" ON "GiornalePorto" . "ID" = "dossiers_1" . "id"
WHERE "GiornalePorto" . "ID" >= dbms_random.value(0, 1)
ORDER BY "GiornalePorto" . "DataOraConcordata"

MS Access <=> MS SQL Server

SELECT tblCategory.idCategory, tblPlants.ItemKey, "Product Name: " & tblCategory.name 
FROM tblPlants 
INNER JOIN tblCategory ON (tblPlants.PatentInfo = tblCategory.charCategoryName) AND (tblPlants.IDNumber = tblCategory.idCategory) 
WHERE ((("idCategory") < "IDNumber")) 
ORDER BY tblCategory.idCategory;
SELECT TOP 100 tblCategory.idCategory, tblPlants.ItemKey, "Product Name: " + tblCategory.name 
FROM tblPlants 
INNER JOIN tblCategory 
ON (tblPlants.PatentInfo = tblCategory.charCategoryName) AND (tblPlants.IDNumber = tblCategory.idCategory) 
WHERE ((("idCategory") < "IDNumber")) 
ORDER BY tblCategory.idCategory;

MS Access <=> MySQL

SELECT tblCategory.idCategory, tblPlants.ItemKey, "Product Name: " & tblCategory.name 
FROM tblPlants 
INNER JOIN tblCategory ON (tblPlants.PatentInfo = tblCategory.charCategoryName) AND (tblPlants.IDNumber = tblCategory.idCategory) 
WHERE ((("idCategory") < "IDNumber")) 
ORDER BY tblCategory.idCategory;
SELECT tblCategory.idCategory, tblPlants.ItemKey, CONCAT("Product Name: ", tblCategory.name) 
FROM tblPlants 
INNER JOIN tblCategory ON (tblPlants.PatentInfo = tblCategory.charCategoryName) AND (tblPlants.IDNumber = tblCategory.idCategory) 
WHERE ((("idCategory") < "IDNumber")) 
ORDER BY tblCategory.idCategory;

MS Access <=> PostgreSQL

SELECT tblCategory.idCategory, tblPlants.ItemKey, "Product Name: " & tblCategory.name 
FROM tblPlants 
INNER JOIN tblCategory ON (tblPlants.PatentInfo = tblCategory.charCategoryName) AND (tblPlants.IDNumber = tblCategory.idCategory) 
WHERE ((("idCategory") < "IDNumber")) 
ORDER BY tblCategory.idCategory;
SELECT tblCategory.idCategory, tblPlants.ItemKey, "Product Name: " || tblCategory.name 
FROM tblPlants 
INNER JOIN tblCategory ON (tblPlants.PatentInfo = tblCategory.charCategoryName) AND (tblPlants.IDNumber = tblCategory.idCategory) 
WHERE ((("idCategory") < "IDNumber")) 
ORDER BY tblCategory.idCategory;
Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments