Oracle: Case: I / Elsef Query

Question

I execute the select statement as shown below

Select case 
         when count(*) = 0 then 'Pass' 
       end as Test_Result
  from Table Name 
 where condition;

I need a conclusion below

If count(*)=0 then Result should be 'Pass'

Else I need the result of this query. I mean the conclusion below one

Select Count(*)
  from Table Name 
 where condition;

Answer 1

One option is to execute a query without a condition WHERE, and then use conditional aggregation to get the counter that appears in ELSE.

SELECT CASE WHEN COUNT(*) = 0 THEN 'Pass'
            ELSE CAST(SUM(CASE WHEN condition THEN 1 ELSE 0 END) AS VARCHAR2(30))
       END AS Test_Result
FROM yourTable

Answer 2

One of the methods:

Setup:

create table emptyTab ( a number);
create table notEmptyTab ( a) as ( select 1 from dual );

This should do the trick:

SQL> select case
  2           when count(*) = 0 then 'Pass'
  3           else to_char(count(*))
  4         end
  5  from emptyTab
  6  where a = 1       ;

CASEWHENCOUNT(*)=0THEN'PASS'ELSETO_CHAR(
----------------------------------------
Pass

SQL> select case
  2           when count(*) = 0 then 'Pass'
  3           else to_char(count(*))
  4         end
  5  from notEmptyTab
  6  where a = 1;

CASEWHENCOUNT(*)=0THEN'PASS'ELSETO_CHAR(
----------------------------------------
1

SQL> select case
  2           when count(*) = 0 then 'Pass'
  3           else to_char(count(*))
  4         end
  5  from notEmptyTab
  6  where a = 2;

CASEWHENCOUNT(*)=0THEN'PASS'ELSETO_CHAR(
----------------------------------------
Pass