## SQL Book Club – Any recommendations?

I got a note from Steven Feuerstein the other day about a group of developers in Stockholm starting an SQL Book Club. What a great idea :-) Anyway, they had asked Steven if he had any recommendations for good books on Advanced SQL. And Steven asked me the same question...

(more...)

(more...)

## Top selling items – revisited in 12c

April last year I blogged about TOP-N reporting using Top selling items as example. In Oracle 12c we now have a new FETCH FIRST syntax, so in this post I'll revisit the Top selling items example showing where and how FETCH FIRST can be used and where you still need (more...)

## Active Data Guard and Invalidations

To provide data source for our datawarehouse (in a seperate MS SQL database, god help it, but that's beside the point :-), we have a setup where we have several views where the datawarehouse connection user has been granted select rights.

When we got Active Data Guard in the spring, (more...)

When we got Active Data Guard in the spring, (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...)

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:

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...)

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:

And some data as well:

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)

(more...)

## I’m evaluated…

UKOUG 2012 evaluations have arrived - I think I did OK :-).

On a scale from 1 to 6 my scores were:

On a scale from 1 to 6 my scores were:

- Topic: 5.5
- Content: 5.5
- Presentation skills: 4.83
- Quality of slides: 5
- Value of presentation: 5.67

I'm quite happy with those scores - particularly that the 6 people that filled out evaluation schemas thought they got a lot of value from the presentation. The skills score is fair, I had expected a bit less as I know I am not world class presenter - but I hope practice makes better :-)

The comments also makes (more...)

The comments also makes (more...)

## Formspider comes to Denmark

For the Danish Oracle User Group (DOUG) I'll be hosting a Formspider event in Copenhagen January 21st 2013. I look forward to seeing Yalim Gerger demonstrate this alternative to APEX or ADF or Forms.

The event will take place in my company's classroom at Banestrøget 17, 2. th., 2630 Tåstrup, which is 2 minutes walk from Høje Tåstrup train station.

Program for the afternoon:

The event will take place in my company's classroom at Banestrøget 17, 2. th., 2630 Tåstrup, which is 2 minutes walk from Høje Tåstrup train station.

Program for the afternoon:

- 13:30 - 14:30 Introducing Formspider, the Web 2.0 framework for PL/SQL developers

- 14:40 - (more...)

## Thank you, UKOUG 2012

So, I'm about to leave UKOUG 2012. I had a good time and learned quite a bit from the smart people gathered in Birmingham ;-)

Thank you to those attending my presentation on analytic functions - I hope you learned something from it. If you need to take a closer look, both presentation and scripts can be found here.

Birmingham Airport next stop...

Thank you to those attending my presentation on analytic functions - I hope you learned something from it. If you need to take a closer look, both presentation and scripts can be found here.

Birmingham Airport next stop...

## Analytic FIFO multiplied – part 3

This is part 3 of a three part posting on analytic FIFO picking of multiple orders. Part 3 shows how to combine the FIFO developed in part 1 with the analytics used for the better route calculation in an earlier blog post.

We use the same tables and same data as part 1, so read part 1 for the setup.

When combining the FIFO for multiple orders with the route calculation, we get this nice piece of sql:

We use the same tables and same data as part 1, so read part 1 for the setup.

When combining the FIFO for multiple orders with the route calculation, we get this nice piece of sql:

with orderlines as (

select o.ordno

, o.item

, o.qty

, nvl(sum(o.qty) over (

partition by o. (more...)

## Analytic FIFO multiplied – part 2

This is part 2 of a three part posting on analytic FIFO picking of multiple orders. Part 2 shows an alternative way of doing the same thing as part 1 did - but this time using recursive subquery factoring in Oracle v. 11.2.

We use the same tables and same data as part 1, so read part 1 for the setup.

And just to recap - here's the picking list developed in part 1:

We use the same tables and same data as part 1, so read part 1 for the setup.

And just to recap - here's the picking list developed in part 1:

with orderlines as (

select o.ordno

, o.item

, o.qty

, nvl(sum(o.qty) over (

partition by o.item

order by o. (more...)

## Analytic FIFO multiplied – part 1

I have blogged before about Analytic FIFO picking as well as talked about it at KScope12 and will do again at UKOUG2012.

A few days ago Monty Latiolais, the president of ODTUG, had a need to do this - not just for one order which he already had developed the technique for, but for multiple orders, where the FIFO picking for the second order should not consider the inventory that was already allocated to the first order, and so on.

So here is a three-part demo of how to do this.

First we setup the same inventory as (more...)

A few days ago Monty Latiolais, the president of ODTUG, had a need to do this - not just for one order which he already had developed the technique for, but for multiple orders, where the FIFO picking for the second order should not consider the inventory that was already allocated to the first order, and so on.

So here is a three-part demo of how to do this.

First we setup the same inventory as (more...)

## Ready for UKOUG2012

I think I am about ready for UKOUG conference 2012. Hope I haven't forgotten something :-)

- Train ticket to airport - check
- Plane ticket to get to Birmingham - check
- Hotel reservation - check
- UKOUG2012 registration - check
- Planned my agenda - check
- Chairing a session - check
- Uploaded presentation for my session - check
- Discovered where to get Oracle beer near ICC - check

Yup - checklist done :-)

If you're interested, come to my session Wednesday Dec. 5th at 12:10 and see if I can speak fast enough to go though 130 slides in an hour showing these (more...)

## A bit of fun expressing ratios

Sometimes answering questions on the OTN forum leads to a little fun trying to be creative in SQL ;-) A user wished to express a ratio as 1:1 or 1:2. That lead to a little fun with CONNECT BY on DUAL for recursion.

This is the SQL I ended up creating:

This is the SQL I ended up creating:

with r as (

select .2233 ratio from dual union all

select .2500 ratio from dual union all

select .2666 ratio from dual union all

select .2750 ratio from dual union all

select .2828 ratio from dual

)

select r.ratio ratio_num

, (

select to_char(

max(level)

, 'TM9'

(more...)

## RANGE BETWEEN and leap years

Answering a question on the OTN forum was a bit tricky to get an analytic sum using a RANGE BETWEEN that would handle leap years, but in the end I came up with a workaround that satisfies the requirement. Along the way I realized why there are two different INTERVAL datatypes :-)

Let's make a sales table to demo this:

And populate with some data for specific days in 2010, 2011 and 2012:

Let's make a sales table to demo this:

create table sales (

day date

, qty number

)

/

And populate with some data for specific days in 2010, 2011 and 2012:

begin

insert into sales values (date '2010-10-01', 1);

insert into sales values (date '2010-10-02', 2);

insert (more...)

## Find your way with HttpUriType and Google Maps

Recently I read Duke Ganote writing about using UTL_HTTP to get stock quote from Yahoo. (Duke must have a thing for authorities, particularly Marshalls of Legoredo ;-) Anyway, I posted a comment how to do a similar thing with HttpUriType.

And that reminded me that long time ago I reminded myself that I should blog about how we use HttpUriType to query driving distance and time from Google Maps. (I have even tried to submit abstract to KScope and UKOUG on getting data with HttpUriType, UTL_HTTP or UTL_FTP, but no go so far...)

Let's imagine Larry needs directions to (more...)

And that reminded me that long time ago I reminded myself that I should blog about how we use HttpUriType to query driving distance and time from Google Maps. (I have even tried to submit abstract to KScope and UKOUG on getting data with HttpUriType, UTL_HTTP or UTL_FTP, but no go so far...)

Let's imagine Larry needs directions to (more...)