Oracle ' ' operator with case statements?-CodePudding

I was looking at some oracle code and found these case statements are joined with a operator? what does it do here? Is it possible to avoid ' ' and do the same thing?

         case
           when t1.id is not null
             then 1
           else
             0
         end  
         case
           when t2.id is not null
             then 2
           else
             0
         end  
         case
           when t3.id is not null
             then 4
           else
             0
         end filter,

CodePudding user response：

While your case represents an ordinary aritmetical operation of addition, an alternative way is to use NVL2() function, which combines null and non-null cases as conditionals, to make it shorter such as

SELECT NVL2(t1.id,1,0) NVL2(t2.id,2,0) NVL2(t3.id,4,0) AS filter...

CodePudding user response：

Or, yet another option, DECODE:

select
  decode(t1.id, null, 0, 1)  
  decode(t2.id, null, 0, 2)  
  decode(t3.id, null, 0, 4) as filter
from ...

CodePudding user response：

I don't see any obvious way of making the logic more terse, but here is an explanation.

case when t1.id is not null then 1 else 0 end     -- returns 1 or 0
case when t2.id is not null then 2 else 0 end     -- returns 2 or 0
case when t3.id is not null then 4 else 0 end     -- returns 4 or 0

Each CASE expression above returns an integer value, either 1, 2, or 4 should the particular id be not NULL, or zero otherwise. The operator is simply summing these integer expressions together.

CodePudding user response：

Is it possible to avoid ' ' and do the same thing?

In fact, yes. Assuming the ids all have the same type, you can use a lateral join:

from . . . -- all your existing stuff here
     cross join lateral
     (select count(id) as cnt
      from (select t1.id from dual union all
                   t2.id from dual union all
                   t3.id from dual
           ) x
     ) x