Blog

SQL quiz revisited: ROW_NUMBER, LAST_VALUE, FIRST_VALUE and the SQL knights value problem.

Thank you all for your entries to the recent quiz. The guys from Packt should be shortly sending out the e-books to the winners. Today I will walk you through some of the possible solutions and also briefly touch upon performance of these. Last week’s contest was based on a classic SQL problem. Anthony Molinaro, ...

Read More

SQL Quiz: Win two e-books (Essbase & BPEL)

We’ll do something completely different today and have a quiz. And you can even win something. Thanks to the guys at Packt Publishing I have two e-books to giveaway. Oracle Essbase 9 Implementation Guide and Business Process Execution Language for Web Services When you visit their website make sure to have a look at their free ...

Read More

TIME dimension script Oracle

SELECT n AS time_id, TO_CHAR(to_date(n,'SSSSS'),'HH24') AS hour, TO_CHAR(to_date(n,'SSSSS'),'MI') AS minute, TO_CHAR(to_date(n,'SSSSS'),'SS') AS second FROM ( SELECT level-1 n FROM DUAL CONNECT BY LEVEL <= 86400 ) 123456789101112 SELECT   n AS time_id,   TO_CHAR(to_date(n,'SSSSS'),'HH24') AS hour,   TO_CHAR(to_date(n,'SSSSS'),'MI') AS minute,   TO_CHAR(to_date(n,'SSSSS'),'SS') AS secondFROM (   SELECT      level-1 n   FROM      DUAL   CONNECT BY LEVEL <= 86400)

Read More

Date dimension script with Oracle SQL

The script below will create a date dimension in just one SQL statement. We don’t use performance-killer nonsense such as cursors, functions etc. We just pick a start date and the number of days we want to create and with a bit of magic of the Connect By clause and the NUMTODSINTERVAL function we do ...

Read More