Oracle Migration - LAG function equivalent.

db*****@yahoo.com wrote:

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

*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

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

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

Serge Rielau wrote:

db*****@yahoo.com wrote:

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

*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

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

Mark Townsend wrote:

Serge Rielau wrote:

db*****@yahoo.com wrote:

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

*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

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

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
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab