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

Uli Bethke November 15, 2009

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

TIME dimension script Oracle

Uli Bethke October 30, 2009

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

Uli Bethke February 24, 2009

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
1 2