FW: a workaround for the 18 digit-ish disfunction of bitand (update)

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Thu, 19 Jul 2007 16:53:01 -0400
Message-ID: <027501c7ca46$cd127990$1100a8c0@rsiz.com>

 

It turns out values of 16 and 32 for "v" keep all the bits in the 20 decimal digit range. Someone please chime in if there is a better way to formulate this. I've got this feeling in the back of my head that I've got a brain cramp. Of course this is all on a 32-bit version, so your mileage may vary. I didn't check metalink for bugs/patches either. Maybe that's the brain cramp. This is an oldish version of 10 - 10.1.0.4.0.    

SQL> select

v*(bitand(trunc(99999999999999999999/v,0),trunc(99999999999999999999/v,0)))

+

  2            bitand(mod  (99999999999999999999,v)  ,mod


(99999999999999999999/v,0)) value

  3

v*(bitand(trunc(99999999999999999999/v,0),trunc(99999999999999999999/v,0)))

value1

  4            bitand(mod  (99999999999999999999,v)  ,mod


(99999999999999999999/v,0)) value2


  5         v

  6 from break18

  7 order by value;  

                VALUE                VALUE1                VALUE2

V

• --------------------- ---------------------

  ---

 36893488147419103231  36893488147419103228  00000000000000000003
00000000000000000004

 73786976294838206463  73786976294838206456  00000000000000000007
00000000000000000008

 99999999994596929302 99999999994043039744 00000000000553889558 00000000008589934592

 99999999999445424011 99999999994043039744 00000000005402384267 00000000017179869184

 99999999999463046234 99999999998338007040 00000000001125039194 00000000002147483648

 99999999999999434285 99999999998338007040 00000000001661427245 00000000004294967296

 99999999999999999999  99999999999999999968  00000000000000000031
00000000000000000032

 99999999999999999999  99999999999999999984  00000000000000000015
00000000000000000016

 

8 rows selected.  

SQL> select
v*(bitand(trunc(10846370260800065548/v,0),trunc(9368617832122679304/v,0))) +

  2 bitand(mod (10846370260800065548,v) ,mod
(9368617832122679304,v)) value

  3 from break18;  

                VALUE

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

 09368617832122679304

 09368617832122679304

 09368617832122679304

 09368617832122679304

 09368617832122679304

 09368617832122679304

 09368617832122679304

 09368617832122679304  

8 rows selected.  

SQL>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jul 19 2007 - 15:53:01 CDT