Search code examples
sqlsql-serveroracle

Select a constant value using same SQL statement in SQL Server and Oracle

I need to write a query which will be run against SQL Server and Oracle. A query just selects a constant.

In SQL Server, I can do this:

SELECT 1 AS Value;

And in Oracle:

SELECT 1 AS Value FROM DUAL;

But the database is not known at runtime. Is it possible to have one SQL query for both DBMS?

Queries above work fine, but I need to unify the syntax.

Solution

  • For Oracle 23c the "From Dual" could be omitted and

    Select 1 as CONST

    would work in both databases
    For older Oracle versions one way to do it is to create dual table in SQL Server

    create table Dual (dummy Char(1));
Insert Into Dual Values('x');

    ... after this

    Select 1 as CONST From Dual;

    would work in both databases
    But that all doesn't make much sense since there are so many other differencies.