There are more or less differences in terms of function between various types of databases. When we are trying to migrate reports from one database to another, the existing SQL containing a different set of function syntax need to be converted at the same time. Usually, a SQL data set in a report is static and fixed. As an automatic conversion is impossible, we have to modify corresponding SQL statement manually and replace the old with the new.

Yet the auto-conversion of SQL in report between databases can be achieved with esProc SPL. SPL designs a set of standard SQL query syntax that has a wealth of built-in functions (and the number is growing) for describing as many common computations as possible. The SPL sqltranslate function can translate the standard SQL to different database SQL dialects.

For example, below is a standard SQL statement:

SELECT CLIENT, YEAR(ORDERDATE), SUM(AMOUNT)

           , COUNT(ORDERID)

FROM ORDERS

GROUP BY CLIENT, YEAR(ORDERDATE)

HAVING SUM(AMOUNT) > 2000

We translate it using .sqltranlate("MYSQL") and get the following statement:

SELECT CLIENT, YEAR(ORDERDATE), SUM(AMOUNT)

           , COUNT(ORDERID)

FROM ORDERS

GROUP BY CLIENT, YEAR(ORDERDATE)

HAVING SUM(AMOUNT) > 2000

And get the following statement if we use .sqltranslate("ORACLE") to translate it:

SELECT CLIENT, EXTRACT(YEAR FROM ORDERDATE), SUM(AMOUNT)

           , COUNT(ORDERID)

FROM ORDERS

GROUP BY CLIENT, EXTRACT(YEAR FROM ORDERDATE)

HAVING SUM(AMOUNT) > 2000

The standard SQL function can select the right functions according to the specified database.

esProc SPL also provides JDBC for being called by Java. We can call it from a reporting tool as we call a stored procedure.

We write the code for SQL conversion as a SPL script (sqlquery.dfx):

In which parameter dbtype is the database type (here it is the data source name), and parameter SQL is the SPL standard SQL.

Then we call the SPL script in the reporting tool’s data set (in the same way as we call the stored procedure, and parameter pass-in is allowed):

{call sqlquery(?,?)}