Is the Else part of Case When statement ignored in PL/SQL?

Question

Why "else" part is not working I don't understand. Normally it should returns reverse of numbers if case statement not provided.

Here is my code;

select Id,
       case 
         when mod(Id,10)<(Id/10) 
           then (cast((Id/10) as number(5))*10)+(mod(Id,10)) 
         else 
           mod(Id,10)*10+(Id/10)  
       end Col
from digits

Sample data;

CREATE TABLE Test
(
    Id INT
);

insert into test
select 21 from dual
UNION ALL
select 12 from dual
UNION ALL
select 34 from dual
UNION ALL
select 43 from dual
UNION ALL
select 29 from dual
UNION ALL
select 92 from dual;

Thanks in advance..

That is a CASE expression in a SQL statement. Not a CASE statement in a PL/SQL procedure — user330315
– user330315, Commented May 24, 2018 at 6:29
mod(Id,10) will never return any value larger than 9. With your data ((Id/10)*10) will always be 10 or larger. I.e. else will never be reached. — jarlh
– jarlh, Commented May 24, 2018 at 6:37
@jarlh was just writing exactly the same thing! Although if Id < 10, ((Id/10)*10) will be 0. — Nick
– Nick, Commented May 24, 2018 at 6:38
Thanks friends, pls correct my code for me. I get this status in pl sql, same problem is not valid for t-sql. — aprkturk
– aprkturk, Commented May 24, 2018 at 6:42
@aprkturk . . . Just look at the results of the arithmetic (rextester.com/ERKWV25819). The case expression is doing the logic correctly. — Gordon Linoff
– Gordon Linoff, Commented May 24, 2018 at 11:43

Florin Ghita · Accepted Answer · 2018-05-24 13:02:54Z

1

For reversing the integers with two digits the case should look like(notice the trunc function):

select  
   Id,
   case 
     when mod(Id,10)<(Id/10) 
       then trunc(Id/10)*10+mod(Id,10) --this is the number itself, Id
     else 
       mod(Id,10)*10+trunc(Id/10)  --reverse :)
   end Col

edited May 24, 2018 at 13:02

answered May 24, 2018 at 12:45

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Simon Sellick · Accepted Answer · 2018-05-24 13:31:35Z

1

@aprkturk you mentioned T-SQL; here is Florin's answer translated:

select  
   Id,
   case 
     when Id % 10 < Id / 10
       then Id / 10 * 10 + Id % 10  -- this is the number itself, Id
     else 
       Id % 10 * 10 + Id / 10       -- reverse :)
   end Col
from @i
order by Id

What is this for? It seems an odd sort of thing to want to do...

answered May 24, 2018 at 13:31

Simon Sellick

1981 silver badge13 bronze badges

Collectives™ on Stack Overflow

Is the Else part of Case When statement ignored in PL/SQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related