Is it possible to nest CASE-statement into WHERE clause?

All we need is an easy explanation of the problem, so here it is.

I am developing an application with FastAPI. It contains a function that brings two variables into a SQL query using the sqlalchemy.sql.text as shown here.

SELECT ATTR3
FROM TABLE
WHERE ATTR1 = TO_NUMBER(:var1)
  AND (CASE
          WHEN TO_NUMBER(:var1) NOT IN (1460, 1478, 1481) THEN 'ATTR2 IS NULL'
          ELSE ATTR2 = TO_NUMBER(:var2)
      END);

So, after pasting the variables, the SQL query will look as following

SELECT ATTR3
FROM TABLE
WHERE ATTR1 = TO_NUMBER('1460')
  AND (CASE
           WHEN TO_NUMBER('1460') NOT IN (1460, 1478, 1481) THEN 'ATTR2 IS NULL'
           ELSE ATTR2 = TO_NUMBER('000')
       END);

However, I am getting the following error:

ORA-00905: missing keyword

So, now I am wondering how can I resolve this issue? As well as if it is possible to nest a CASE-statement into the WHERE clause?

Here is the logic of m SQL statements:

Is it possible to nest CASE-statement into WHERE clause?

I have seen some related topics, but they were not helpful.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

It is not clear to me what you are trying to do, so this is a wild guess:

SELECT ATTR3
FROM TABLE
WHERE ATTR1 = TO_NUMBER(:var1)
  AND ((TO_NUMBER(:var1) NOT IN (1460, 1478, 1481) AND ATTR2 IS NULL)
       OR
       (TO_NUMBER(:var1) IN (1460, 1478, 1481) AND ATTR2 = TO_NUMBER(:var2))
       ;

I added some redundant parentheses for clarity. Using a union is another possibility:

SELECT ATTR3
FROM TABLE
WHERE ATTR1 = TO_NUMBER(:var1)
  AND TO_NUMBER(:var1) NOT IN (1460, 1478, 1481) 
  AND ATTR2 IS NULL
UNION
SELECT ATTR3
FROM TABLE
WHERE ATTR1 = TO_NUMBER(:var1)
  AND TO_NUMBER(:var1) IN (1460, 1478, 1481) 
  AND ATTR2 = TO_NUMBER(:var2)

Add a db<>fiddle or similar to your post, together with the expected output if this did not solve your problem.

Method 2

In this case I think you can avoid to use CASE, just use AND, OR.

Given the next example:

create table t (atr1 int, atr2 int);
insert into t values
(1, 15),(2, 30),(3, 45),(1, 30),(2, null),(3, 90);

declare @var1 int = 2;
declare @var2 int = 30;

You could write your query as:

select
    *
from
    t
where
    atr1 = @var1
    and
    (
      (@var2 not in (3, 4) and atr2 is null)
      or
      (@var2 in (3, 4) and atr2 = @var2)
    );
atr1 | atr2
---: | ---:
   2 | null

db<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply