Why is the else condition triggered when the when condition is fulfilled?

select t.customer_type, t.balance_no
from test12 t
where t.customer_type=&a1 and t.currency= 
(case when &a2 in t.currency then &a2 
 else 'ALL' 
 end);

I take a number for customer type and a string for currency from the user and return the current row. If the currency entered by the user is not available in the table then I should return the row in which the currency value is ‘all’. And if the currency entered by the user exists in the table, I need to return the row corresponding to it. But in my code, it doesn’t work like that. If the currency entered by the user exists in the table then it returns rows that match both that currency and the else condition. For example, if a2=’USD’ then it returns 2 rows, t.currency=’USD’ and t.currency=’ALL’, if a2=’ALL’ or a value doesn’t exist in the table then it works truly, returns 1 row, t.currency=’ALL’. What is the problem? Why case-when doesn’t work truly?

Answer

Your condition and its case expression are evaluated for every row in the table (that matched the customer type, if the optimiser filters that first). It isn’t matching both the when and else for any single row.

• For the row with ‘AZN’ neither the when or else result match, so that row is rejected.
• For the row with ‘USD’ the when result is matched, so that row is included.
• For the row with ‘ALL’ the else result is matched, so that row is included.

So if the currency exists you always match both that and ‘ALL’, and if it doesn’t you only match ‘ALL’.

The in t.currency suggests you might think that will search the whole table every time, but it is only looking at the data in one row at a time (so should be = not in).

Assuming you only expect a single match you could look for either value, order based on why it matched, and pick the first row:

select t.customer_type, t.balance_no
from test12 t
where t.customer_type = &a1
and t.currency in (&a2, 'ALL')
order by case when t.currency = &a2 then 1 else 2 end
fetch first 1 row only;

Otherwise you can explicitly apply the logic you seems to be expecting, checking the whole table with exists; something like:

select t.customer_type, t.balance_no
from test12 t
where t.customer_type = 1
and (
  t.currency = &a2
  or
  (
    t.currency = 'ALL'
    and not exists (
      select null
      from test12
      where customer_type = 1
      and currency = &a2
    )
  )
)

or if you want to stick with in:

select t.customer_type, t.balance_no
from test12 t
where t.customer_type = &a1
and t.currency = 
 case
   when &a2 in (select currency from test12 where customer_type = &a1)
   then &a2
   else 'ALL'
 end;

Note that the in is now searching the whole table, via a subquery, not just looking at the current row.

db<>fiddle with fixed values instead of substitution variables.

Incidentally, you might find it easier to quote those, i.e. '&a2', then you don’t need to include the quotes when prompted for the currency value.