sql - return a row of constant values if row does not exist

Question

I want to be able to return a row of none, none, 0 if no row is returned from query. I have this SQL:

select first, last, count(address)
from employee
where last in ('james', 'smith', 'hankers')
group by first, last
union all 
select 'none', 'none', 0
from dual
where not exists (select * from employee where last in ('james', 'smith', 'hankers'));

From DB, an entry for james and smith exists but no entry exists for hankers.

But this query only returns for when an entry exists. Does not return none, none, 0.

What am I doing wrong here?

EDIT: In this example, I am passing 3 hard coded values as last, but I would like to know a work-around if we were passing the values in as a list parameter like so (:last) through getJdbcTemplate.

you can use rowcount, like here : http://stackoverflow.com/questions/2884996/simple-check-for-select-query-empty-result — aguetat, Nov 12 '16 at 08:13
Okey, I support aguetat, but his example in msSql, But In oracle, you can select count for checking. — vipin, Nov 12 '16 at 08:57

score 0 · Answer 1 · answered Nov 12 '16 at 08:12

0

The NOT EXISTS is applied taking into consideration all of the values listed. So, if any one value exists then the NOT EXISTS is not satisfied.

As a work-around you can use an in-line table with the specified values and left join your original table to it:

select coalesce(t2.first, 'none'), 
       coalesce(t2.last, 'none'), 
       count(t2.address)
from (
   select 'james' as last
   union all
   select 'smith'
   union all
   select 'hankers') t1
left join employee t2 ON t1.last = t2.last
group by coalesce(t2.first, 'none'), 
         coalesce(t2.last, 'none')

If there is no match, as is the case for last='hankers', then count(t2.address) evaluates to 0 and thus 'none', 'none', 0 is returned.

answered Nov 12 '16 at 08:12

Giorgos Betsos

71,379
9
63
98

I see. Thank you. However, I should have clarified in original post, but what would be the workaround if we didn't know the exact size of the `last` parameters? In the example, I am passing hard coded 3 values as `last`, but in my actual SQL I do not know the values and am passing a list paramater like this `in (:last);` – qollers Nov 12 '16 at 08:18
@qollers Another workaround would be to populate a temp table with all the values. The info about the last names has to be stored in a relational structure of some sort if you want to retrieve it. – Giorgos Betsos Nov 12 '16 at 08:31

vipin · Answer 2 · 2016-11-13T13:44:41.950

0

May this will help you,

_count NUMBER(10);

select count(*) into _count
from employee
where last in ('james', 'smith', 'hankers');

if(_count > 0)
then
   select first, last, count(address) c
   from employee
   where last in ('james', 'smith', 'hankers')
   group by first, last
else
   select 'none' first, 'none' last, 0 c   from dual
end if

edited Nov 13 '16 at 13:44

answered Nov 12 '16 at 09:07

vipin

357
3
9

`_count NUMBER(10);` are we declaring number variable here? Sorry, am beginner at SQL – qollers Nov 12 '16 at 15:37
If you are using stored procedure to perform db operations, you can declare variables. – vipin Nov 13 '16 at 13:46

sql - return a row of constant values if row does not exist

2 Answers2