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.

You're kind of new at SQL, but yours is already quite elaborate... — pascal, Jul 29 '10 at 18:31
The *bold value* was lost in some edit... Anyway, when you replace the bold value... by what? — pascal, Jul 29 '10 at 18:32
Is there some constraint in your design, linking TABLE1, TABLE2, TABLE3, which says that `acct_id` is in `select acct_id from TABLE2 group by acct_id having count(*)=1` implies that `select count(*) from TABLE3 where acct_id=TABLE3.acct_id` returns 1? — pascal, Jul 29 '10 at 18:33
@pascal: probably when he uses a constant instead of the bold value. — FrustratedWithFormsDesigner, Jul 29 '10 at 18:38

Thomas · Accepted Answer · 2010-07-29T19:13:22.370

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.

edited Jul 29 '10 at 19:13

answered Jul 29 '10 at 18:29

Thomas

63,911
12
95
141

1

I don't think the comma is the root cause of "result is more than one row", probably the result of copy/paste. – FrustratedWithFormsDesigner Jul 29 '10 at 18:37
Wow, awesome response. Thanks so much. The actual query, has a lot more junk, I paired it down to focus on my issue, hence the syntax errors such as the comma and table alias. I'm working against db2 and I TOP 1 gives me an error. Here's what doesn't make sense, this acct_id only appears once, so how can it return more than one row? It seems like the THEN statement is ignoring the single value. – YeeHaw Jul 29 '10 at 18:40
1

@FrustratedWithFormsDesigner: You're right - it's that the query in the THEN isn't guaranteed to only return one row in what the OP posted. – OMG Ponies Jul 29 '10 at 18:57
@FrustratedWithFormsDesigner - RE: TOP. That is my mistake. I did not see that you were using DB2. For DB2, you need to use Fetch First 1 Rows Only. – Thomas Jul 29 '10 at 19:13

score 1 · Answer 2 · edited Nov 20 '11 at 00:19

1

Then ( Select name 
       From TABLE3 
       Where acct.acct_id = TABLE3.acct_id
       Fetch First 1 Rows Only)

Fetch is not accepting in CASE statement - "Keyword FETCH not expected. Valid tokens: ) UNION EXCEPT. "

edited Nov 20 '11 at 00:19

animuson

53,861
28
137
147

answered Feb 22 '11 at 17:26

Roush

11
1

score 0 · Answer 3 · answered Jul 29 '10 at 18:29

0

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.

answered Jul 29 '10 at 18:29

James Curran

101,701
37
181
258

score 0 · Answer 4 · answered Jul 29 '10 at 18:29

0

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!

answered Jul 29 '10 at 18:29

kbrimington

25,142
5
62
74

Agreed, but why when I replace TABLE1.acct_id with a literal value, query runs fine? – YeeHaw Jul 29 '10 at 18:45
I'd expect so. Try out the select count(name) and inspect the results, looking for counts greater than 1. I think it'll narrow things down for you. – kbrimington Jul 29 '10 at 20:21

score 0 · Answer 5 · answered Jul 29 '10 at 18:35

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).

Use of CASE statement values in THEN expression

5 Answers5