Use of CASE statement values in THEN expression

Question

I am attempting to use a case statement but keep getting errors. Here's the statement:

select TABLE1.acct,
        CASE
          WHEN TABLE1.acct_id in (select acct_id 
                                    from TABLE2 
                                group by acct_id 
                                  having count(*) = 1 ) THEN
             (select name 
                from TABLE3 
               where TABLE1.acct_id = TABLE3.acct_id)
          ELSE 'All Others'
        END as Name
   from TABLE1

When I replace the TABLE1.acct_id in the THEN expression with a literal value, the query works. When I try to use TABLE1.acct_id from the WHEN part of the query, I get a error saying the result is more than one row. It seems like the THEN expression is ignoring the single value that the WHEN statement was using. No idea, maybe this isn't even a valid use of the CASE statement.

I am trying to see names for accounts that have one entry in TABLE2.

Any ideas would be appreciated, I'm kind of new at SQL.

Answer 1

First, you are missing a comma after TABLE1.acct. Second, you have aliased TABLE1 as acct, so you should use that.

Select acct.acct
    , Case 
        When acct.acct_id in ( Select acct_id 
                                From TABLE2 
                                Group By acct_id 
                                Having Count(*) = 1 ) 
            Then ( Select name 
                    From TABLE3 
                    Where acct.acct_id = TABLE3.acct_id
                    Fetch First 1 Rows Only) 
        Else 'All Others' 
        End as Name 
From TABLE1 As acct

As others have said, you should adjust your THEN clause to ensure that only one value is returned. You can do that by add Fetch First 1 Rows Only to your subquery.

Answer 2

select name from TABLE3 where TABLE1.acct_id = TABLE3.acct_id

will give you all the names in Table3, which have a accompanying row in Table 1. The row selected from Table2 in the previous line doesn't enter into it.

Answer 3

Must be getting more than one value.

You can replace the body with...

(select count(name) from TABLE3 where TABLE1.acct_id = TABLE3.acct_id)

... to narrow down which rows are returning multiples.

It may be the case that you just need a DISTINCT or a TOP 1 to reduce your result set.

Good luck!

Answer 4

I think that what is happening here is that your case must return a single value because it will be the value for the "name" column. The subquery (select acct_id from TABLE2 group by acct_id having count(*) = 1 ) is OK because it will only ever return one value. (select name from TABLE3 where TABLE1.acct_id= TABLE3.acct_id) could return multiple values depending on your data. The problem is you trying to shove multiple values into a single field for a single row.

The next thing to do would be to find out what data causes multiple rows to be returned by (select name from TABLE3 where TABLE1.acct_id= TABLE3.acct_id), and see if you can further limit this query to only return one row. If need be, you could even try something like ...AND ROWNUM = 1 (for Oracle - other DBs have similar ways of limiting rows returned).