Fun with SQL!

I wrote this query a while back. was able to get the results as I wanted, but found it more sensible to change the DB design and create another table to simplify the query to a greater extent.

I am sure I was not drunk when I wrote this :D but something was wrong!

select psm.BT_SEGMENT_ID || ' (' || BTS.ORIG_SEGMENT_DESC || ')' as segment, crazy_tab.count

from partner_segment_maps psm join 

(

select (substr(segment_name, instr(','||segment_name,',',1,seq),

instr(','||segment_name||',',',',1,seq+1) - instr(','||segment_name,',',1,seq)-1)) Segments, count(*) as count

from 

(select kpi_id, replace(

DBMS_LOB.substr(context, instr(context, 't=')-instr(context, 'id=')-4, instr(context, 'id=')+3) 

, '&' || 'id=',',') segment_name

from kpi k

where trunc(k.date_created) in (to_date(:date_created, 'MM/DD/YYYY'))

and k.campaign_detail_id =  :cd_id) ak_test, (select level seq from dual  connect by level <= 500) seqgen

where instr(','||segment_name||',',',',1,seq+1) > 0 group by (substr(segment_name, instr(','||segment_name,',',1,seq),

instr(','||segment_name||',',',',1,seq+1) - instr(','||segment_name,',',1,seq)-1))

) crazy_tab

on psm.PARTNER_SEGMENT_NAME = crazy_tab.SEGMENTS

JOIN BT_SEGMENTS BTS ON BTS.BT_SEGMENT_ID = PSM.BT_SEGMENT_ID

Bitwise Operations in SQL

I have been working on a ticket that requires a privilege to be enabled on a POS device for all the retailers. doing my research, I found that there were about 14 privileges for a POS device that you can enable/disable by checking/unchecking the flag from the POS maintenance UI we have. I have to do this for 16,000 POS devices and using the UI to do this is not a feasible approach. I have to do a mass update in the Database.
Further looking at it, I found that the privileges are clubbed together and calculated and stored in the Database as a long decimal bitmap value. each of the 14 privileges are represented as static final long variable of 16 bit hexadecimal values like,
 long POS_PRIVILEGE_BIT_BALANCE_INQUIRY = 0x1000000000000000L.
these are used while calculating the long decimal bitmap.
Now to enable the Balance inquiry bit, all I have to do is convert the about hexadecimal value to decimal and do a bitwise OR with the existing decimal bitmap from teh database. this way if the balance inquiry bit is enabled the result of bitwise OR will be same, and if it is disabled, the result will be a decimal number with this bit enabled. so I wont be breaking anythign if the balance inquiry is already enabled for any POS devices.
SQL provides a function called bitAND(x,y). with bitAND you can calculate bitOR as x + y - bitAND(x,y). you can calculate bitwise XOR, NAND, NOR etc as well.

my query for bitwise OR would be something like this,
select (-3511118859488722944 + 1152921504606846976) - bitAND(-3511118859488722944, 1152921504606846976) as bitOR from dual;

-3511118859488722944 is the existing privileges in decimal in the database.
1152921504606846976 is the balance inquiry bit in decimal.

how do I test whether what I am doing is correct ? I will test for 2 things.
1. I do a bitwise AND (&) with the decimal bitmap and the decimal balance inquiry value, If I get the result as 0 that means the balance inquiry bit is disabled. If the result is non zero than the bit is selected.
2. I take the decimal bitmap from database and update the balance inquiry bit in it by doing a bitwise OR with the balance inquiry static value. i get a new decimal number. now if I do a bitwise XOR with the previous value and new value I must get the balance inquiry decimal value. because thats the only bit I changed. awesome application of XOR here!

My colleague Bobe helped me in understanding the application of these bitwise operations. Also I learned using windows calculator for all these number conversion and bitwise operations.

here is an article to understand what bitwise OR is http://en.wikipedia.org/wiki/Exclusive_or. most of us know this and much more from our books but applying them is more pleasing!

Partitioned outer join in Oracle 10 and over

This is really an amazing thing. you got to report on data which is not in the database! yes i mean it. one of my report query shows counts grouping by on member and month of the year. something like this,
ABC 01-2001 54
ABC 02-2001 45
ABC 04-2001 12
DEF 09-2001 76
DEF 10-2001 65
you can see that there is no data for ABC for the month of march. this is what the QC team here needs to catch. what i was asked to do is to display another row like ABC 03-2001 0.
well my current query is 40 lines with two subqueries outerjoined. its complicated and i have to make it over complicated now! i tried several nasty ways and was not even close to what was needed. then googled and found this article..

http://www.oracle.com/technetwork/issue-archive/o14asktom-084433.html

this guy is doing exactly what i needed. but i could hardly grasp his explanation.
meanwhile i shared my issue with one of the DBA and she was also scratching her head for some time now. she suggested few techniques and after a while forwarded me this artcile, which i feel is worth sharing.

http://www.oracle-developer.net/display.php?id=312

this is what i need and its explained very clearly.

i am back to work trying to mess around with my query now!

first post!

To create this blog has been in my mind for so long now. better late than never.

browsing today, these were some of the interesting sites i have found.

1. http://techcrunch.com/

2. http://www.viki.com/

3. http://technofriends.in/

4. http://foursquare.com/

5. http://www.savevideodownload.com/

the link # 5 is to save a youtube video to a file. thats pretty cool! i just saved a song that i couldn't find after searching for an hour on the internet! the other links are just some technical and timepass stuff.