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