you want to BIT_OR multiple rows. For example you have a table with 3 rows that you want to aggregate with BIT_OR

1010 (10)

1100 (12)

0110 (6)

=========

1110 (14)

Let’s try

with t as (

select 10 n from dual union all

select 12 from dual union all

select 6 from dual)

select

utl_raw.cast_to_binary_integer(

sys.mvaggrawbitor(

utl_raw.cast_from_binary_integer(

n

)

)

) N

from t;

N

---

14

It is that easy !

disclaimer: mvaggrawbitor is not documented

Hi Laurent,

>> It is that easy !

Ha! Maybe easy for you!

This is very impressive and elegent solution to a complex problem! You are a worthy scholar indeed . . . .

Hi Laurent,

Any idea why Oracle has mvaggrawbitor but no aggregated “and” and “not” operators? (At least on 10.2.0.2)

If they only have enough space for just one aggregating binary operator, XOR would be a better option.

according to the name, it seems to be an internal function used for materialized views

or:

SQL> with t as

2 ( select *

3 from mytable

4 model

5 dimension by (row_number() over (order by null) rn)

6 measures (n)

7 rules

8 ( n[any] order by rn desc = nvl(n[cv()+1],0) + n[cv()] – bitand(nvl(n[cv()+1],0),n[cv()])

9 )

10 )

11 select n

12 from t

13 where rn = 1

14 /

N

———-

14

the same without subquery

select n

from mytable

model

return updated rows

dimension by (row_number() over (order by null) rn)

measures (n)

rules iterate (10000)

until (presentv(n[ITERATION_NUMBER+3],1,2)=2)

( n[1] = n[1] + nvl(n[ITERATION_NUMBER+2],0) -

bitand(n[1],nvl(n[ITERATION_NUMBER+2],0)))

/

That’s even better.

And doing some final (?) little optimizations:

SQL> select n

2 from mytable

3 model

4 return updated rows

5 dimension by (rownum r)

6 measures (n)

7 rules iterate (10000) until n[iteration_number+3] is null

8 ( n[1] = n[1] + n[iteration_number+2] – bitand(n[1],n[iteration_number+2])

9 )

10 /

N

———-

14

Now, it really starts looking that easy π

Regards,

Rob.

well, if the table contains only one row, it will return null and if the third row is a NULL, it will exit too early (therefore UNTIL PRESENTV)

with mytable as (

select 1 p, 63 n from dual union all

select 2,1 from dual union all

select 2,1 from dual union all

select 2,null from dual union all

select 2,1 from dual)

select p,n

from mytable

model

return updated rows

partition by (p)

dimension by (row_number() over (partition by p order by null) rn)

measures (n)

rules iterate (10000)

until (presentv(n[ITERATION_NUMBER+3],1,2)=2)

(

n[1] = n[1] + nvl(n[ITERATION_NUMBER+2],0) -

bitand(n[1],nvl(n[ITERATION_NUMBER+2],0))

)

/

`P N`

---------- ----------

1 63

2 1

Good points.

Although the preconditions were not mentioned, your solution handles them and is therefore more robust.

Regards,

Rob.

Oh

Good solution!

I post homeMadeBitOr π

with t as (

select 10 n from dual union all

select 12 from dual union all

select 6 from dual)

select

sum(distinct(bitand(32,n)))+

sum(distinct(bitand(16,n)))+

sum(distinct(bitand( 8,n)))+

sum(distinct(bitand( 4,n)))+

sum(distinct(bitand( 2,n)))+

sum(distinct(bitand( 1,n))) as N

from t;

Nice approach, I wished I found it myself π

oops

We can use solution which is more simple.

with t as (

select 10 n from dual union all

select 12 from dual union all

select 6 from dual)

select

max(bitand(32,n))+

max(bitand(16,n))+

max(bitand( 8,n))+

max(bitand( 4,n))+

max(bitand( 2,n))+

max(bitand( 1,n)) as N

from t;

this makes sense!

and for AGGREGATE_BITAND, we could use MIN then!

for AGGREGATE_BITXOR, I would then use :

`select`

mod(sum(sign(bitand(32,n))),2)*32+

mod(sum(sign(bitand(16,n))),2)*16+

mod(sum(sign(bitand( 8,n))),2)* 8+

mod(sum(sign(bitand( 4,n))),2)* 4+

mod(sum(sign(bitand( 2,n))),2)* 2+

mod(sum(sign(bitand( 1,n))),2) n

from t;

http://oraclesqlpuzzle.hp.infoseek.co.jp/8-20.html

My site mentions these solutions π (written by Japanese language)

Thank you π

wow! this is exactly the same, I like this π

arigato!