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
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
EXISTS returns TRUE or FALSE, you can't then test if that is IN ( ... )
answered Jan 13, 2010 at 11:07 AM
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
answered Jan 13, 2010 at 09:47 AM
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?
Okay thanks will have to come up with something around the case statement. Thanks again for help.
answered Jan 13, 2010 at 11:22 AM