0

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:

logic

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

0
4

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.

0
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.