SQL to return fixed data in both SQL Server and Oracle

I 开发者_运维问答need a common select statement that returns a fixed value / row without the need of tables, which has to work with both Oracle & Sql Server.

eg for Oracle I know I can use:

select 'O' AS INDICATOR from DUAL;

But this won't work on Sql Server.

Can this be done with the same SQL on both Oracle & SQL Server?

---

AFAIK, you'll need different queries, unless you can find a table that exists both on the SQL Server and on the Oracle Server.

Oracle uses the DUAL table for dummy queries, while the syntax to just select a constant on SQL server is a bit simpler:

select 'O' as Indicator

will return a one-row recordset.

P.S. If you intend to write just standard SQL and have it work on both SQL Server and Oracle, note that there are lots and lots of differences, even if you do not use database-side code (stored procedures and functions).

Off the top of my head, some things that are different:

• Case statement syntax
• NVL vs IsNull
• Null sorting behaviour
• Data conversion functions
• String manipulation functions
• etc, etc.

---

You can't select data in Oracle without from statement. So you need to have a table in Oracle (common practice is to use standard table - Dual). The best solution if you really need to run same query on both database servers is to create Dual table with only one row in MS SQL. But really it's better to use different queries for different servers (maybe via some abstraction layer).

---

Use a common table expression (CTE) e.g.

WITH D (INDICATOR)
     AS 
     (
      SELECT * 
        FROM (
              VALUES ('O')
             ) T (c1)
     )
SELECT INDICATOR 
  FROM D;

Or more simply in line:

SELECT *
 FROM (
       VALUES ('O')
      ) D (INDICATOR)

---

You can create the DUAL table in SQL Server:

CREATE TABLE DUAL (DUMMY NVARCHAR(1) NOT NULL);

INSERT INTO DUAL VALUES ('X');

and then use the same query as in Oracle:

select 'O' AS INDICATOR from DUAL;