Oracle Migration - LAG function equivalent.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

db2sysc@yahoo.com - 24 Mar 2005 22:21 GMT

What is the equivalent of ORACLE LAG FUNCTION in DB2 on Z/OS and UDB on
LUW??

LAG FUNCTION RETURNS THE PREVIOUS ROW.

select id, createdate, name old_name, lag(name) over(partition by id
order by id,createddate desc)new_name
from
(select id,name,createdate
from people
union
select id,name,createdate
from people_arch)
order by createddate asc

Reply to this Message

Serge Rielau - 24 Mar 2005 22:49 GMT

> What is the equivalent of ORACLE LAG FUNCTION in DB2 on Z/OS and UDB on
> LUW??
[quoted text clipped - 10 lines]
> from people_arch)
> order by createddate asc

*Squinting hard* Looks like syntactic sugar for MAX() OVER() or MIN()
OVER() using a window of size 1 preceeding by one....
DB2 for zOS does not support OLAP, I suspect you will need to use a join
in that case to achieve portable code.

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

Mark Townsend - 25 Mar 2005 04:25 GMT

>> What is the equivalent of ORACLE LAG FUNCTION in DB2 on Z/OS and UDB on
>> LUW??
[quoted text clipped - 18 lines]
> Cheers
> Serge

Just as an FYI - LAG and LEAD actually take an row offset and default
value as well. So LAG(name, 2, 'Fred') would return the value from 2
rows before the current row and FRED if you had stepped out of bounds.
If not specified (which is the example given here), the offset is
defaulted to 1 and the default value as NULL

Reply to this Message

Serge Rielau - 25 Mar 2005 05:10 GMT

>>> What is the equivalent of ORACLE LAG FUNCTION in DB2 on Z/OS and UDB on
>>> LUW??
[quoted text clipped - 24 lines]
> If not specified (which is the example given here), the offset is
> defaulted to 1 and the default value as NULL

Presuming name is not nullable...
MIN(CASE WHEN name IS NOT NULL THEN name ELSE 'Fred' END) OVER (...)
(?) Get's trickier when to emulate when name is nullable.
OLAP is quite powerful. Unfortunately not many users master it.

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

mirof007@gmail.com - 25 Mar 2005 01:07 GMT

As Serge said, no direct equivalent in DB2 on LUW, but you can simulate
it using a single row window specification, something similar to:

select id, createdate, name old_name,
max(name) over(partition by id order by id, createddate desc
rows between 1 preceding and 1 preceding) new_name
from
(select id,name,createdate
from people
union
select id,name,createdate
from people_arch)
order by createddate asc

Similarly, LEAD can be simulated with "between 1 following and 1
following".

Hope this helps,
Miro

Reply to this Message