|
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
(comments are locked)
|
|
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')))
(comments are locked)
|
|
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 ...) )
(comments are locked)
|
|
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))
(comments are locked)
|
|
Okay thanks will have to come up with something around the case statement. Thanks again for help.
(comments are locked)
|
