CODEWITHSUNDEEP
sqloracle-databaseconditional-statements
Soon
Soon

Reputation: 501

Oracle-how to modify condition to select all if condition fails?

My goal is to select all the data including NULL, if conditon fails. How to modify condition?

WITH DD AS (
SELECT 1 A FROM DUAL
UNION
SELECT NULL A FROM DUAL
)SELECT * FROM DD
WHERE A =2 ????? ;  -- How to modify this condition?

Expected result

A
---
1
NULL

Upvotes: 0

Views: 57

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

If you want to refer to the "table" only once, you can use window functions:

select dd.a
from (select dd.*,
             sum(case when a = 2 then 1 else 0 end) over () as cnt
      from dd
     ) dd
where a = 2 or cnt = 0;

This can be helpful when the "table" is actually a complicated expression and you want to ensure that it is not evaluated multiple times.

Upvotes: 0

forpas
forpas

Reputation: 164089

With UNION ALL and NOT EXISTS:

WITH DD AS (
  SELECT 1 A FROM DUAL
  UNION
  SELECT NULL A FROM DUAL
)
SELECT * FROM DD
WHERE A =2
UNION ALL
SELECT * FROM DD
WHERE NOT EXISTS (
  SELECT 1 FROM DD
  WHERE A = 2
)

The query after UNION ALL will return all the rows of DD only if WHERE A = 2 fails.

Upvotes: 1

Related Questions