CODEWITHSUNDEEP
sqloracle-database
Tal Angel
Tal Angel

Reputation: 1788

Oracle - select inside select

I have a query that works on MSSQL, but when I run it on Oracle DB it fails:

SELECT ( CASE WHEN NOT EXISTS(SELECT NULL FROM DA_TRANSACTION) THEN 0 ELSE 1 END ) AS isEmpty

The error that I get:

Error in SQL Query: ORA-00923: FROM keyword not found where expected

How to modify my query so it will work in Oracle as well?

Upvotes: 0

Views: 107

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

Oracle needs a FROM clause. And it provides a one-row table for this purpose:

SELECT ( CASE WHEN NOT EXISTS(SELECT NULL FROM DA_TRANSACTION) THEN 0 ELSE 1 END ) AS isEmpty
FROM DUAL;

If you want an efficient version that works in either database (and you are using Oracle 12C+):

select count(*) as isEmpty
from (select 1
      from da_transaction
      offset 0 fetch first 1 row only
     ) t

Upvotes: 1

Related Questions