Sql query to select records only when count(column)=1

Question

I am trying to retrieve records from oracle 10g express.

I want to execute :

select name|| '=' || id from literals where name='vge_1'

only when count(vge_1) is equal to 1.

else I want to display an error.

I tried following query, but its giving

ORA-00905: missing keyword

THe query I tried is as follows:

select case(name) 
when count('vge_1') then (select name|| '=' || id from literals where name='vge_1';)
else Errror 
end
from Literals where name='vge_1';

Thanks for your help in advance.

Answer 1

Try this SQL Fiddle:

select distinct case 
    when l2.c = 1 then l1.name || '=' || l1.id
    else 'Error'
end as name_id
from literals l1,
(
    select name, count(name) as c
    from literals
    where name = 'vge_1'
    group by name
    having count(name) = 1
) l2
where l1.name = l2.name(+)
and l1.name = 'vge_1'
;

The inner query is roughly same as the other answers. The outer query uses a left outer join (+) to determine if the inner query contains a match for your count() restriction.

Note that you must update the name in two places when running this query.

Answer 2

Instead of the case add HAVING count(name)=1 in the end of th query

Answer 3

Try this:

select b.id,a.* from
(select name from Literals 
where name='vge_1'
group by name
having count(name)=1)a,Literals b
where b.name=a.name