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 '10 at 08:08 AM in Default

siopold gravatar 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 '10 at 11:07 AM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(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 '10 at 09:47 AM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(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 '10 at 10:15 AM

siopold gravatar 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 '10 at 11:22 AM

siopold gravatar 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x39

asked: Jan 13 '10 at 08:08 AM

Seen: 2536 times

Last Updated: Jan 13 '10 at 09:44 AM