Home » SQL & PL/SQL » SQL & PL/SQL » Why calculated a later invalid statement in a case-when after first true clause found? (PL/SQL Developer 11.0.6.1776 01.108073)
icon11.gif  Why calculated a later invalid statement in a case-when after first true clause found? [message #677818] Mon, 14 October 2019 02:45 Go to previous message
Arthurbaa
Messages: 6
Registered: October 2019
Junior Member
Case-When works in a way, theoretically, that after the first True statement none of next clauses will be executed.
Nevertheless, in the following example the else will be tried to calculate in spite that the first then clause was true.

select case when 1=1 then 1
else sum(1/zero) end as eredmeny
from
(select 0 as zero from dual union all
select 0 as zero from dual union all
select 0 as zero from dual) d;

will result in "ORA-01476: Divisior is equal to zero"

The same happens if division by tero is a False Then clause:

select case when 1=1 then 1
when 1=0 then sum(1/zero)
else 2 end as eredmeny
from
(select 0 as zero from dual union all
select 0 as zero from dual union all
select 0 as zero from dual) d;

It seems as if calculation within sum() would be executed even if it is in an inactive part which should not be calculated.
In case division by zero exits sum(), no Error happens:

This code runs correctly:

select case when 1=1 then 1
else sum(1)/zero end as eredmeny
from
(select 0 as zero from dual union all
select 0 as zero from dual union all
select 0 as zero from dual) d
group by zero;

Is it a rule that aggregations will be run even in inactive case-when clauses, if they should have no impact on result?

Report message to a moderator

[Message index]
 
Read Message icon11.gif
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Read Message
Previous Topic: Oracle - path passing by multiple nodes
Next Topic: Syhthesizing
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 06:22:40 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.