CODEWITHSUNDEEP
sqloracle-database
ShB
ShB

Reputation: 319

select another statement If no rows returned Oracle

I am weak at SQL. I have a small table with 3 columns and basic select statement. 

    select intl, loc FROM test_table where p.country = 'UAE'

but in some cases there are no rows returned and in those cases I want to select another statement from the same table.

I found the below query but I cant get it to work.

    select intl, loc FROM test_table
    where p.country = 'UAE'
    union all
    select 'intl','Loc' from dual 
    where not exists ( select intl, loc FROM test_table where p.country = 'Other' )

I know its simple but I'm stuck for hours. Thanx

Upvotes: 0

Views: 7581

Answers (3)

EyfI
EyfI

Reputation: 1005

If I understand your requirements correctly, a bit different approach may be this:

SELECT intl, loc from test_table WHERE p.country = 
(CASE WHEN (SELECT intl FROM test_table WHERE p.country='UAE' AND ROWNUM=1) IS NOT NULL THEN 'UAE' 
ELSE 'Other' END);

It looks like you want to use either one value in WHERE p.country = or another. So this CASE determines whether selecting with the first p.country value returns something (we have to limit the results with ROWNUM=1 to get exactly one result, otherwise the case will not work - it expects one value after WHEN). If it does (IS NOT NULL), then we use that value in our WHERE, otherwise we use a different value specified after the ELSE.

Upvotes: 1

Himanshu Kriplani
Himanshu Kriplani

Reputation: 121

select intl, loc 
from test_table
where p.country = 'UAE'
union all
select intl, loc from test_table 
where not exists ( select intl, loc from test_table where p.country = 'UAE' );

This is better.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269445

The query you seem to want is:

select intl, loc 
from test_table
where p.country = 'UAE'
union all
select 'intl', 'Loc' from dual 
where not exists ( select intl, loc from test_table where p.country = 'UAE' );

The country for the NOT EXISTS should be the same as in the original query.

Upvotes: 1

Related Questions