NOCOUG Second SQL Challenge

Working with the second NoCOUg SQL Challenge. In the magazine there can be found also "advice for an Oracle Beginner" articles - worth reading.




Here is my five cents to towards the problem. Another answers may be found from Iggy Fernandez blog comments.

When I found the challenge, there were already some published answers to the riddle. So I started with minimizing the starting set. Got rid of nulls in the first place. And after a while ended up with a hierarchical query. On a way I draw a Graphviz picture of the riddle data. Maybe that visualizes some paths I was trying to follow trying to figure out alternative solutions. SQL commands for creating the required data.

with aa as (
select word1, word2, word3, word2 gr
  from riddle
 where word1 is not null
), bb as (
select gr,pre,word
  from aa
unpivot (word for pre in (word1 as 1, word2 as 2, word3 as 3))
), cc as (
select gr,pre,word
     , first_value(case when pre = 3 and word != gr then gr end ignore nulls)over(partition by word) bg
     , first_value(case when pre = 1 and word != gr then gr end ignore nulls)over(partition by word) ag
     , min(pre)over(partition by word) mi
     , max(pre)over(partition by word) ma
  from bb
), dd (gr,mi,ma,pre,word,ord)as (
select gr,mi,ma,pre,word,cast(2 as varchar2(10))
  from cc
 where cc.pre=2 and cc.bg is null and cc.ag is null
 union all
select cc.gr,cc.mi,cc.ma,cc.pre,cc.word
     , dd.ord||case when cc.pre = 1 then cc.mi else cc.ma end
from dd inner join cc on cc.pre in (1,3) and dd.word = cc.gr
)
select listagg(dd.word,' ')within group(order by rpad(dd.ord,10,'2'))
  from dd
;

Update 8.3.2011
Ordering with rpad seems like so borrowed from the riddle_tree. So here is another solution that maintains the ordering number while browsing the tree.

with aa as (
select word1, word2, word3, word2 gr
  from riddle
 where word1 is not null
), bb as (
select gr,pre,word
  from aa
unpivot (word for pre in (word1 as 1, word2 as 2, word3 as 3))
), cc as (
select gr,pre,word
     , first_value(case when pre = 3 and word != gr then gr end ignore nulls)over(partition by word) bg
     , first_value(case when pre = 1 and word != gr then gr end ignore nulls)over(partition by word) ag
     , min(pre)over(partition by word) mi
     , max(pre)over(partition by word) ma
  from bb
), dd (gr,mi,ma,pre,word,nord,lv)as (
select gr,mi,ma,pre,word,2222222,1
  from cc
 where cc.pre=2 and cc.bg is null and cc.ag is null
 union all
select cc.gr,cc.mi,cc.ma,cc.pre,cc.word
     , dd.nord+(cc.pre-2)*power(10,6-dd.lv)
     , dd.lv+1
from dd inner join cc on cc.pre in (1,3) and dd.word = cc.gr
)
select listagg(dd.word,' ')within group(order by dd.nord)
  from dd 
;

revoke through a db link

Why would someone do such a thing? Following will end up an active a user session stuck waiting "SQL*Net message from dblink".

sqlplus /nolog

connect / as sysdba


drop user a cascade;

drop user b cascade;


create user a identified by a;

grant create session to a;

grant create procedure to a;

grant create database link to a;


create user b identified by b;

grant create session to b;


connect a/a

declare 
 comm varchar2(200);
begin 
 select 'create database link b connect to b identified by b using '''||
        sys_context('userenv','db_unique_name')||'''' into comm 
   from dual;
 execute immediate comm;
end;
/


create or replace procedure p as
begin
execute immediate 'revoke execute on p from b';
end;
/


grant execute on p to b;

exec a.p@b

not exists null

I wrote earlier about not in and null. Be careful also when using not exists predicate together with a sub query resulting nulls.

SQL> select 1 from dual where not exists (select 1 from dual where 1=0);

         1
----------
         1

SQL> select 1 from dual where 1=0;

no rows selected



SQL> select 1 from dual where not exists (select max(1) from dual where 1=1);

no rows selected

SQL> select max(1) from dual where 1=1;

    MAX(1)
----------
         1



SQL> select 1 from dual where not exists (select max(1) from dual where 1=0);

no rows selected

SQL> select max(1) from dual where 1=0;

    MAX(1)
----------


SQL> select 1 from dual where not exists (select null from dual);

no rows selected

SQL> select null from dual;

N
-

In the sub query there is a row but it is null, unknown. So not exists null evaluates to false. More about the issue in responses to a oracle-l mailing list post.