08.12.2007

Pagination with DB2

Modern web pages are widely using pagination, and the most common database in LAMP environment, MySQL, ist supporting this by the LIMIT offset, row_count parameter. With DB2 one has to use a little trickery to come to a similar result.

Imagine a list of blog entries, ordered by time, displaying the latest on top. This list is easily created by a VIEW:

Create View
blog.vw_entries_500latest
as
Select
ROW_NUMBER() OVER(order by date,time desc) as ROWNO,
id,date,time,title
From
blog.tb_entries_all
order by
date,time desc
Fetch
first 500 rows only;
Now lets load the data for the 11th page, where each page should list 25 blog entry titles:

Select
rowno,id,date,time,title
from
blog.tb_entries_500latest
where
rowno > 25 * (11 - 1)
fetch
first 25 rows only ;
This should do the trick.


Diese Seite zu Mister Wong hinzufügen

05.12.2007

1st business day in month

Quiet often events are logged in databases for the purpose of statistics or billing. Maybe there's the need to know which day of the month was the first business day. Here's a way to find out with DB2.

Imagine a table log.events, containing a column named when of type date. Basically you'll rely on the function DayOfWeek(), which returns a minimum of 1 in case the given date is sunday, and a maximum of 7 for saturday:

select
( 15 - ( DayOfWeek( when - (Day(when) - 1) days) ) ) / 7
+
( 2 * ( DayOfWeek( when - (Day(when) - 1) days) / 7 ) )
from
log.events ;

With some arithmetic trickery the following code returns

  • 3 (1st of month is saturday, 1st business day is monday 3rd)
  • 2 (1st of month is sunday, 1st business day is monday 2rd)
  • 1 (1st of month and 1st business day is within tuesday to friday)

Diese Seite zu Mister Wong hinzufügen