473,549 Members | 2,239 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Oracle Migration - LAG function equivalent.

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,created ate
from people
union
select id,name,created ate
from people_arch)
order by createddate asc

Nov 12 '05 #1
4 10277
db*****@yahoo.c om 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,created ate
from people
union
select id,name,created ate
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
Nov 12 '05 #2
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,created ate
from people
union
select id,name,created ate
from people_arch)
order by createddate asc

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

Hope this helps,
Miro

Nov 12 '05 #3
Serge Rielau wrote:
db*****@yahoo.c om 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,created ate
from people
union
select id,name,created ate
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

Nov 12 '05 #4
Mark Townsend wrote:
Serge Rielau wrote:
db*****@yahoo.c om 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,created ate
from people
union
select id,name,created ate
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
Nov 12 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
3898
by: Azhar Bilgrami | last post by:
Dear Hi: Hope to see u in good health. " I want to migrate a database which is currently running Unix as Operating System and Oracle ver 6 as Database, it is also using oracle froms version 3 (text base) as Front End" Now here is what I want to do " I want to migrate that database from Oracle for Unix to Oracle for Linux With retaining...
1
8802
by: Phil Hindmoor | last post by:
Hi, I am sure if anyone can help me, you guys can! I am an Informix Developer, moving to Oracle 8i and later databases. I am struggling to find the Oracle equivelant to many of the useful Informix features that I have come to rely upon. I have looked through the Oracle/Infomix groups and some helpful users have posted some solutions, but...
2
14920
by: Amin Schoeib | last post by:
Hi, Like I see there is no equivalent to the Oracle decode Function In Postgres.Is there maybe somebody who wrote decode as a Function? Schoeib 4Tek Gesellschaft für angewandte Informationstechnologien mbH Schoeib Amin Tel. +49 (0) 69 697688-132
3
1703
by: db2sysc | last post by:
1. In the oracle trigger we have a SINGLE INSERT STORED PROCEDURE CALLED MUTLIPLE TIMES with different values. But when changed to DB2, it gives SQL -746 ?? Any help?? 2. In oracle we have Create or replace TYPE TEST_TYPE AS OBJECT ( ....,....)
4
1743
by: db2sysc | last post by:
All: What is the equivalent of Oracle BULK COLLECT in DB2 UDB? I do see MUTIPLE-ROW SELECT in DB2 ON Z/OS, where in INTO hostvariables can be declared as arrays. Is this possible in UDB V8.2?
1
1882
by: db2sysc | last post by:
All. We have LOT of variables declared in the Oracle package as ORACLE CONSTANTS like, v_test CONSTANT INTEGER=1; When converting to DB2, MTK changes each of these CONSTANTs into functions. This typically increased the count of the functions.
0
1058
by: usa777 | last post by:
I use 'CURRENT SERVER' special register to return the name of the database I am connected to in DB2. Is the function corresponding to 'CURRENT SERVER' in Oracle?
2
2028
by: manindra | last post by:
Recently we migrated our product from MS-SQL 2000 to Oracle 9i. We see lot of performance degradation due to migration. Some times complex queries are hitting 10 fold slower than SQL Server. Reasons could be, 1.The Oracle Server with respect to configuration may have issues. 2.The SQL server optimized queries are not suitable for Oracle(The...
0
2709
Saii
by: Saii | last post by:
Hello Can somebody point me to a good document on PostgreSQL to Oracle migration steps. I have checked various sites including postgresql.org but I need to have a basic head start to evaluate the complexity involved. Any help is appreciated. Thanks
0
7743
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7986
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
7832
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6074
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5391
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
3518
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3499
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1965
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
786
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.