What I’m doing end of June 2013

It is that time again...

Time to go mingle with the best of the best, learn much, teach what I can, suffer information overload, have fun, enjoy life, and much much more...

In short - time for ODTUG KScope13 \o/ \o/

Hotel and flight has been booked half a year (more...)

PL/SQL Challenge Authorship

The PL/SQL Challenge site by Steven Feuerstein is great for learning various SQL and PL/SQL techniques. I am one of the quiz authors - I write most of the SQL quizzes (and one or two PL/SQL quizzes now and then.)

That means there is now accumulated quite a bit of my work as quizzes - each quiz demonstrating some knowledge of SQL. I could replicate this work as blog posts as well, but instead it is now possible for you to search all my quizzes on PL/SQL Challenge.

I have added that link to the right-hand menu of the (more...)

ROWS versus default RANGE in analytic window clause

I have talked at KScope about the difference between ROWS and RANGE in the analytic window clause, but haven't yet blogged about it. Recently while working out a quiz for the PL/SQL Challenge I discovered yet another reason for remembering to primarily use ROWS and only use RANGE when the actual problem requires it.

From my KScope presentation examples here is a simple case of a rolling sum of salaries:

select deptno
, ename
, sal
, sum(sal) over (
partition by deptno
order by sal
) sum_sal
from scott.emp
order by deptno
, sal

    DEPTNO ENAME             SAL     (more...)

The KScope Charitable Dinner Raffle

Do you want a chance for a dinner with me at KScope13 in New Orleans chatting about SQL? And at the same time get a warm charitable feeling inside helping the volunteers rebuilding New Orleans?

If yes, then read on :-) ...

In January ODTUG started a little competition where you could win a dinner for two at KScope13 in New Orleans by telling about your favorite experience from KScope. I entered a little story from KScope12 where I presented on analytic functions.

Surprise, surprise - I won \o/ ... But as I haven't spotted any other entries in the competition, I (more...)

Recursive subquery graph traversing

In December a user Silpa asked a question on AskTom on "Bi-directional hierarchical query," which inspired me to fool around with recursive subquery factoring (available from version 11.2) giving Silpa a solution which he seemed to find useful. Since then I've fooled around a little more with it, particularly concerning cycles in the graph data.

Silpa gave a table like this for testing:

create table network_table (
origin number
, destination number

And some data as well:

insert into network_table values (11, 12)
insert into network_table values (12, 13)
insert into network_table values (14, 11)