CODEWITHSUNDEEP
sqlsql-server
user8675722
user8675722

Reputation:

Add diferent condition into where clause depending on case condition

If column A is not empty I should add one condition and if it is empty, then I should add another condition. Something like this:

select *
from table t
where case when len(t.A) > 0 then t.A = (select B from anothertable ) 
      else t.C = (select D from anothertable)

As this does not compiles, and I can't use IF clause within WHERE is there any other way to achieve this?

Upvotes: 3

Views: 51

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

We can rephrase the login in the WHERE clause to make it work:

SELECT *
FROM table_t
WHERE
    (LEN(t.A) > 0   AND t.A IN (SELECT B FROM anothertable) ) OR
    (LEN(t.A) <= 0) AND t.C IN (SELECT D FROM anothertable) );

To address the comment by @HoneyBadger if the subqueries on anothertable return more than one record, then this query would error out if we used t.A = (subquery). If you intend to use equals, then you would have to ensure that the subquery only returns a single record. Your suggestion to use WHERE IN might fix the problem.

Upvotes: 4

Related Questions