x

Using CASE and Subselect within WHERE clause

Hi,

I am attempting to use CASE within a WHERE clause and having some trouble. Hopefully somebody can help.

The scenario is as follows

I want to check whether a particular set of data exists in TABLEA or TABLEB depending upon data in columnX

So

                    
    if columnX data is X check tablea                    
    if columnX data is Y check tableb                    

I am thinking something like below but that syntax is not quite correct.

Any help appreciated. Thanks in advance.

Select * from file ...

Where
'
case
when
substr(columnx,1,1) = 'X
then
(select field from tablea
where column1 ='AAAA' and column2 = 'BBBB' and column3 = 'CCCC' and
column4 = 'DDDD') in ('TEST')
when
substr(columnx,1,1) = 'Y
then
(select field from tableb
where column1 ='AAAA' and column2 = 'BBBB' and column3 = 'CCCC' and
column4 = 'DDDD') in ('TEST')
end

Regards

S

more ▼

asked Jan 13, 2010 at 08:08 AM in Default

avatar image

siopold
2 1 1 1

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

EXISTS returns TRUE or FALSE, you can't then test if that is IN ( ... )

            
select * from TABLEA                                                   
where                                                                  
 (substr(ABC,1,1) = 'X'                                                
  and exists                                                           
 (select *             
  from TABLEA                                              
 where COL1 ='AAAAAA'                                                  
   and COL2 = 'BBBBBBB'             
   and COL3 = char(CCC)            
   AND CODE IN ('TESTTEXT')))            
or                                                                     
 (substr(ABC,1,1) = 'Y'                                                
  and exists                                                           
 (select *             
  from TABLEA                                              
 where COL1 ='AAAAAA'                                                  
   and COL2 = 'XXXXXX'             
   and COL3 = char(CCC)            
   AND CODE IN ('TESTTEXT')))            
more ▼

answered Jan 13, 2010 at 11:07 AM

avatar image

Kristen ♦
2.2k 7 11 14

(comments are locked)
10|1200 characters needed characters left

You haven't got any comparison in your where clause.

You have got

            
WHERE            
    CASE WHEN ConditionA THEN ValueA            
         WHEN ConditionB THEN ValueB            
    END            
*** missing comparison test here - e.g. = File.SomeColumn ***            

If you don't want to compare anything, you just want to check if a specific records exists in TableA or TableB then use the EXISTS test

            
WHERE            
       (ConditionA AND EXIST (SELECT * FROM TableA WHERE ...) )            
    OR (ConditionB AND EXIST (SELECT * FROM TableB WHERE ...) )            
more ▼

answered Jan 13, 2010 at 09:47 AM

avatar image

Kristen ♦
2.2k 7 11 14

(comments are locked)
10|1200 characters needed characters left

Hi Kristen,

First thing thanks for your answer. I may have had the code slightly incorrect in my initial post in any case your answer looks better and ALMOST provides me with what I need If you look at my code below I end up with a problem because I need to use IN. It does not seem to like that.

The IN piece is required because the subselect is looking at a table that contains mappings from a value 'CCC' to 'TESTTEXT'

So I have something on my mapping table like

            
0001  TESTTEXT            
0002  TESTTEXT            
0003  TESTTEXT            

so any of the values select from tablea above may be valid?

Any ideas?

            
select * from TABLEA                                                   
where                                                                  
 (substr(ABC,1,1) = 'X'                                                
  and exists                                                           
 (select CODE from TABLEA                                              
 where COL1 ='AAAAAA'                                                  
and COL2 = 'BBBBBBB' and COL3 = char(CCC)) IN ('TESTTEXT'))            
or                                                                     
 (substr(ABC,1,1) = 'Y'                                                
  and exists                                                           
 (select CODE from TABLEA                                              
 where COL1 ='AAAAAA'                                                  
and COL2 = 'XXXXXX' and COL3 = char(CCC)) IN ('TESTTEXT))            

more ▼

answered Jan 13, 2010 at 10:15 AM

avatar image

siopold
2 1 1 1

(comments are locked)
10|1200 characters needed characters left

Okay thanks will have to come up with something around the case statement. Thanks again for help.

more ▼

answered Jan 13, 2010 at 11:22 AM

avatar image

siopold
2 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x12

asked: Jan 13, 2010 at 08:08 AM

Seen: 3887 times

Last Updated: Jan 13, 2010 at 09:44 AM

Copyright 2016 Redgate Software. Privacy Policy