return zero for no rows returned (in a complex query)

Question

i am using following query in oracle. purpose of my query is to return zero if no rows are returned. otherwise i get 'no data found' error. i googled and came across coelese on stackoverflow.i used it as follows:-

SELECT COALESCE( (select sum(transaction_detail.amount)
from
    lookup_state , lookup_city , lookup_bank ,transaction_detail
where
   transaction_detail.bank_id = lookup_bank.bank_id
and
   lookup_bank.city_id = lookup_city.city_id
and
   lookup_city.state_id = lookup_state.state_id
and 
   lookup_state.state_id = 3 
group by
   lookup_state.state_name, lookup_state.state_id) , 0) into state_total_amount ;

i get following error:-

    from not present where expected

What am i doing wrong. Thanks in advance.

Answer 1

Try this:

select coalesce(sum(transaction_detail.amount),0)
into state_total_amount
from
    lookup_state , lookup_city , lookup_bank ,transaction_detail
where
   transaction_detail.bank_id = lookup_bank.bank_id
and
   lookup_bank.city_id = lookup_city.city_id
and
   lookup_city.state_id = lookup_state.state_id
and 
   lookup_state.state_id = 3;