Sql Code Standardization - SqlServer and Oracle

There are many things in world that look strange and bizare to you. One of the obvious reason is that you are confined to one particular flavor of life/technology/environment...It is the same that I felt when I started working on a project that required sql scripts that were written for SQL Server bound application to execute both in Oracle and Sql Server. I know that it is not possible without tweaking. I thought that both are hugely popular and so standardized (Sql-92). NOT SO....

Sql Standardization - Sql Server and Oracle (For: SqlServer 2005 and Oracle 11g.)
By Vinod Kumar Bapatla

I was working on a task to convert SQL (written specifically for SQL Server) embedded in an application written in C and C++ to:
1) ANSI SQL-92 standard or
2) Provide same results in Oracle and SQL Server.

It had many problems. Some of them could be found by searching for solutions, while some could not be found. Here, I am providing solutions to problems that I could overcome.

Problem 1: TOP N key word in SqlServer did not have an equivalent in Oracle.
select TOP N from table order by column
How can it be converted to script that can execute both in Sql Server 2005 and Oracle 11g?


Solution: TOP N is specific implementation for Sql Server and those who work on Sql Server feel strange to know that there is no straight forward implementation in Oracle.
Following is the solution that can give same result and work in both SqlServer and Oracle:
Select * from (
SELECT row_number() over( ORDER by column) as rank,
Table.* FROM Table
) s1
Where s1.rank <= N;



Problem 2: Oracle does not allow a column to LEFT OUTER JOIN with a subquery.
SELECT * FROM
Table1
Left Outer JOIN Table2 ON
Table2.ColumnA = Table1.ColumnB
AND Table2.ColumnC =
( SELECT MAX(D.ColumnC) FROM Table2 D
WHERE Table2.ColumnA = D.ColumnA
AND Table2.ColumnD = ConstantValue )
WHERE
ORDER BY Table1.ColumnA;


If above code is executed in Sql Server, it works perfectly fine. You may wonder what happens in Oracle. It will result in following error message in Oracle:
ORA-01799: a column may not be outer-joined to a subquery
01799. 00000 - "a column may not be outer-joined to a subquery"
*Cause: <expression>(+) <relop> (<subquery>) is not allowed.
*Action: Either remove the (+) or make a view out of the subquery.
In V6 and before, the (+) was just ignored in this case.


Solution: There may be many solutions. But the standard solution is to replace the LEFT OUTER JOIN on table to inline view that has the subquery as well. Little confusing, here is the solution that can work perfectly fine in Oracle and Sql Server:
SELECT * FROM
Table1
LEFT OUTER JOIN (SELECT * FROM Table2
WHERE Table2.ColumnC =
( SELECT MAX(D.ColumnC) FROM Table2 D
WHERE Table2.ColumnA = D.ColumnA
AND Table2.ColumnD = ConstantValue )
) InlineView1 ON
InlineView1.ColumnA = Table1.ColumnB
WHERE
ORDER BY Table1.ColumnA;




Problem 3: UPDATE statement with FROM clause works fine in Sql Server, but it does not work in Oracle.
UPDATE Table1
SET ColumnA = (expression/constant)
FROM Table2
WHERE Table1.ColumnB = Table2.ColumnC


If above code is executed in Oracle, following error is displayed:
ORA-00933: SQL command not properly ended.
The error message does not help you to figure out if it is due to the FROM clause used with UPDATE statement.

Solution: Though there can be many other solutions, considering that above code is just a portion of a very complex query with lots of tables involved, I think following solution works perfectly fine:
UPDATE Table1
SET ColumnA = (SELECT (expression/constant)
FROM Table2
WHERE
Table1.ColumnB = Table2.ColumnC);


Conclusion: Do not consider that Oracle is not robust or efficient database to work with. When we work only on one database, we find it strange that something very common is not available in other databases.
Note: I will update this post whenever I find something challenging while working with both SqlServer and Oracle.

By Vinod Bapatla   Popularity  (1186 Views)