Why in this query I get two results using case? I thought that after the first condition is met, a break follows and the second is not checked at all

select KOD from ap_magazyny mag where 1 =
    case 
    when (value1 like 'ZZ%' and mag.KOD like (select SUBSTR(value1,INSTR(value1,'/',1) 1,3) from dual)) then 1
    when (atrybut_t01 = value2 AND rownum = 1) then 1
    else 0
    end

Result like

CodePudding user response：

I hope my example will help:

with cte as (select case when id = 1 then
                       1
                  else 
                       2
                  end A 
           from test
           where rownum = 1) 
select *
from  test
join cte on test.id = cte.A

You will need to connect it to something because 1 will always be 1 and you will get all the results from the table test.

DEMO

CodePudding user response：

You can use rownum=1 to limit the results.

select * from (
    select KOD 
      from ap_magazyny mag 
     where 1 = case when (value1 like 'ZZ%' 
                         and mag.KOD like (select SUBSTR(value1,INSTR(value1,'/',1) 1,3) 
                                             from dual)
                                           ) then 1
                    when (atrybut_t01 = value2 AND rownum = 1) then 1
                    else 0
               end
    order by case when (value1 like 'ZZ%' 
                         and mag.KOD like (select SUBSTR(value1,INSTR(value1,'/',1) 1,3) 
                                             from dual)
                                           ) then 1
                    when (atrybut_t01 = value2 AND rownum = 1) then 2
               end
)x
where rownum=1