Sql case not returning values

Question

I am trying to choose between two select staments based on number of results. Each statement returns valid data when tested outside the following case statement.

SELECT CASE 
         WHEN Count(base.id) > 0 THEN base.id 
         ELSE (SELECT id 
               FROM   tablea 
               WHERE  pid = @val) 
       END 
FROM   (SELECT id 
        FROM   tablea 
        WHERE  id = @val) AS base

When base.Id >0 it retuns correct, otherwise Null when instead it should have 7 rows from my table

That looks unusually complex - why isn't it just `WHERE id = @val or pid = @val` in a simple `select`? — Damien_The_Unbeliever, Jan 07 '13 at 18:27
The OR returns all values which apply. I only want one condition true with id being the primary condition. Sorry I was not so clear — user13186, Jan 07 '13 at 18:49

a1ex07 · Answer 1 · 2013-01-07T19:02:06.867

4

WHEN COUNT(base.id) >0 is always true, because your FROM filters out rows where id <>@val. Unless I misunderstood what you want, the following gives you desired results:

SELECT id               
FROM   tablea 
WHERE  id = @val OR pid = @val

Update :

SELECT id               
FROM   tablea 
WHERE  id = @val OR (pid = @val 
AND NOT EXISTS (SELECT NULL FROM tablea WHERE id = @val))

edited Jan 07 '13 at 19:02

answered Jan 07 '13 at 18:24

a1ex07

35,290
12
78
96

I want only rows where id= @val if there are none I wish to return only rows where pid=@val – user13186 Jan 07 '13 at 18:44
So in the ELSE case it performs a new query with a different FROM clause – user13186 Jan 07 '13 at 18:52
@user13186 check Alex07's [demo here](http://sqlfiddle.com/#!2/f6b68/1).. ;) +1 Alex07. When [keyword search for comments, questions, answers for entire site is ready I am going to put your answer in brilliant folder :)](http://meta.stackexchange.com/questions/148066/can-i-search-my-comments-using-some-keywords/161869#161869) – bonCodigo Jan 07 '13 at 18:59

score 1 · Answer 2 · answered Jan 07 '13 at 18:29

1

It sounds like you want to select one set of rows or, failing that, a second set. One way is to union the two sets and then pick what you want:

select id from (
  select id, 1 as Selector
    from tablea
    where id = @val
  union
  select id, 2
    from tablea
    where pid = @val ) as Placeholder
  where Selector = case when exists ( select 42 from tablea where id = @val ) then 1 else 2 end

I have assumed that id is not nullable.

answered Jan 07 '13 at 18:29

HABO

13,766
5
33
50

Yes you are correct I am looking for one set or the other but due the tables size I fear a union query would take too long due to finding both sets every time. Is it the only way? – user13186 Jan 07 '13 at 18:47
You could simply do an `if exists ( select 42 from tablea where id = @val )` to decide which query to execute, but you seemed to have your heart set on doing it in one swell foop. – HABO Jan 07 '13 at 20:14
this all happens during a select command onPage load & the value of @val will actually be another nested query causing horrible rutime. This has been a good help though. Thanks – user13186 Jan 07 '13 at 20:42
@user13186 - Have you checked the usual suspects for performance problems? Examine the execution plans, check indexes, update index statistics, ... . Do you need real-time results or could you use a cached result that is up to, say, 5 minutes old? – HABO Jan 07 '13 at 20:49
Can't cache and @val is equal to a three level recursion so I think as long as I get it under 30 sec it just is what it is. – user13186 Jan 07 '13 at 21:08

Sql case not returning values

2 Answers2