Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today. |
|
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience. Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements? Download Free Report! |
Full code listing
It is wonderful article, which shows the capabilities of Oracle 9i SQL. We would appreciate if you can post the complete code listing for this article. We have a similar requirement in one of our projects.
Thanks and keep up the good work!
Listing
select --+cardinality(e 1)
'(((a=1 or b=1) and (y=3 or z=1)) and c=1 and x=5 or Attribute04 NOT IN (1, ''GED'') and y>7)'
expr from dual
), idxs as (
select i
from (select rownum i from dual connect by rownum < 4000) a, src
where i<=LENGTH(EXPR) and (substr(EXPR,i,1)='('
or substr(EXPR,i,1)=' ' or substr(EXPR,i,1)=')' )
--where i<=LENGTH(EXPR) and (substr(EXPR,i,1)='(' or lower(substr(EXPR,i,3))='and'
--or lower(substr(EXPR,i,2))='or' or substr(EXPR,i,1)=')' )
), lbri as (
select i
from idxs, src
where substr(EXPR,i,1)='('
and lower(substr(EXPR,i-3,1))<>'in' -- added code
), rbri as (
select i
from idxs, src
where substr(EXPR,i,1)=')'
and lower(substr(EXPR,i-3,1))<>'in' -- added code
), wtsp as (
select i
from idxs, src
where (substr(EXPR,i,1)=' ')
), begi as (
select i+1 x from wtsp
union all
select i x from lbri
union all
select i+1 x from lbri
), endi as ( -- [x,y)
select i y from wtsp
union all
select i+1 y from rbri
union all
select i y from rbri
), ranges as ( -- [x,y)
select x, y
from begi a, endi b
where x < y
), wffs1 as (
select x, y from ranges r
--
-- bracket balance:
where (select count(1) from lbri where i between x and y-1)
= (select count(1) from rbri where i between x and y-1)
--
-- eliminate '...)...(...'
-- equals when no occurrencies
and (select coalesce(min(i),0) from lbri where i between x and y-1)
<= (select coalesce(min(i),0) from rbri where i between x and y-1)
), wffs as ( -- reduce outer brackets: (..<wffs1>..)
select x+1 x, y-1 y from wffs1 w
where (x in (select i from lbri)
and y-1 in (select i from rbri)
and not exists (select i from rbri where i between x+1 and y-2
and i < all(select i from lbri where lbri.i between x+1 and y-2))
)
union all
select x, y from wffs1 w
where not (x in (select i from lbri)
and y-1 in (select i from rbri)
and not exists (select i from rbri where i between x+1 and y-2
and i < all(select i from lbri where lbri.i between x+1 and y-2))
)
--) select x, y, substr(EXPR, x, y-x) || '!'
--from wffs r, src s where s.num=r.num --and (substr(EXPR, x, y-x) = 'a=1 or b=1' or substr(EXPR, x, y-x) = '(a=1 or b=1)' )
), andi as (
select x i
from wffs a, src s
where lower(substr(EXPR, x, 3))='and'
), ori as (
select x i
from wffs a, src s
where lower(substr(EXPR, x, 2))='or'
), or_wffs as (
select x, y, i from ori a, wffs w where x <= i and i <= y
and (select count(1) from lbri l where l.i between x and a.i-1)
= (select count(1) from rbri r where r.i between x and a.i-1)
), and_wffs as (
select x, y, i from andi a, wffs w where x <= i and i <= y
and (select count(1) from lbri l where l.i between x and a.i-1)
= (select count(1) from rbri r where r.i between x and a.i-1)
and (x,y) not in (select x,y from or_wffs ww)
), other_wffs as (
select x, y from wffs w
minus
select x, y from and_wffs w
minus
select x, y from or_wffs w
), max_or_wffs as (
select distinct x, y from or_wffs w
where not exists (select 1 from or_wffs ww
where ww.x<w.x and w.y<=ww.y and w.i=ww.i)
and not exists (select 1 from or_wffs ww
where ww.x<=w.x and w.y<ww.y and w.i=ww.i)
), max_and_wffs as (
select distinct x, y from and_wffs w
where not exists (select 1 from and_wffs ww
where ww.x<w.x and w.y<=ww.y and w.i=ww.i)
and not exists (select 1 from and_wffs ww
where ww.x<=w.x and w.y<ww.y and w.i=ww.i)
), max_other_wffs as (
select distinct x, y from other_wffs w
where not exists (select 1 from other_wffs ww where ww.x<w.x and w.y<=ww.y)
and not exists (select 1 from other_wffs ww where ww.x<=w.x and w.y<ww.y)
), predicates as (
select 'OR' typ, x, y, substr(EXPR, x, y-x) expr
from max_or_wffs r, src s
union all
select 'AND', x, y, substr(EXPR, x, y-x)
from max_and_wffs r, src s
union all
select '', x, y, substr(EXPR, x, y-x)
from max_other_wffs r, src s
) select * from predicates
;