April 3, 2008 at 10:12 am
I'm trying to convert the following Oracle PL\SQL to MSSQL 2005 and I'm struggling!!
Would anyone happen to know how in the world to re-write the following in SQL Server??
SELECT LEAD(E.ID_PRVD_ORG,1,NULL) OVER (PARTITION BY E.ID_PRSN ORDER BY E.ID_PRSN,E.DT_BGN ) AS LEAD_ID_PRVD_ORG FROM E
where E is my table name
There doesn't appear to be an equivalent function in SQL Server to Oracle's LEAD function.
Any help would be greatly appreciated.
Thanks,
Dave
April 3, 2008 at 10:54 am
I don't think there's a direct equivalent for LEAD()/LAG(). However, you can perform the same type of processing with ROW_NUMBER() and a self-join.
Look at the example for the LEAD function on this page: http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php
The code below implements that example.
drop table #emp
;
create table #emp (empno int, ename varchar(30), job varchar(30), sal int)
insert into #emp values (7369,'SMITH','CLERK',800)
insert into #emp values (7900,'JAMES','CLERK',950)
insert into #emp values (7876,'ADAMS','CLERK',1100)
insert into #emp values (7521,'WARD','SALESMAN',1250)
insert into #emp values (7654,'MARTIN','SALESMAN',1250)
insert into #emp values (7934,'MILLER','CLERK',1300)
insert into #emp values (7844,'TURNER','SALESMAN',1500)
insert into #emp values (7499,'ALLEN','SALESMAN',1600)
insert into #emp values (7782,'CLARK','MANAGER',2450)
insert into #emp values (7698,'BLAKE','MANAGER',2850)
insert into #emp values (7566,'JONES','MANAGER',2975)
insert into #emp values (7788,'SCOTT','ANALYST',3000)
insert into #emp values (7902,'FORD','ANALYST',3000)
insert into #emp values (7839,'KING','PRESIDENT',5000)
;
-- select * from #emp
;
with leader( empno, ename, job, sal, seq )
as
(
select empno, ename, job, sal,
row_number() over (order by sal asc) as seq from #emp
)
select L.*, N.sal as sal_next, N.sal - L.sal
from leader as L left outer join leader as N on L.seq + 1 = N.seq
Evidently LEAD()/LAG() return 0 if no next/prior record is found. Change the code to isnull(N.sal,0) if you want the same behavior.
April 7, 2008 at 7:32 pm
All of it looks good, but I'm lost within the following code, specifically the line starting with "with leader...". I've been working w/SQL for 12+ years and I've never seen this syntax.
with leader( empno, ename, job, sal, seq )
as
(
select empno, ename, job, sal,
row_number() over (order by sal asc) as seq from #emp
)
select L.*, N.sal as sal_next, N.sal - L.sal
from leader as L left outer join leader as N on L.seq + 1 = N.seq
I can't find anything in BOL that helps explain that type of syntax.
The bottom line is that the results seem to be symbolic of what I need, but I just need a little help understanding the first line of the code portion above.
A little help with explaining the "with leader.." line above would most likely get me straightened out here and headed in the right direction.
Thanks so much for the reply!
Dave
April 7, 2008 at 7:48 pm
dmizelle (4/7/2008)
All of it looks good, but I'm lost within the following code, specifically the line starting with "with leader...". I've been working w/SQL for 12+ years and I've never seen this syntax.with leader( empno, ename, job, sal, seq )
as
(
select empno, ename, job, sal,
row_number() over (order by sal asc) as seq from #emp
)
select L.*, N.sal as sal_next, N.sal - L.sal
from leader as L left outer join leader as N on L.seq + 1 = N.seq
I can't find anything in BOL that helps explain that type of syntax.
The bottom line is that the results seem to be symbolic of what I need, but I just need a little help understanding the first line of the code portion above.
A little help with explaining the "with leader.." line above would most likely get me straightened out here and headed in the right direction.
Thanks so much for the reply!
Dave
This "WITH something as (" syntax is called a common table expression - it's new in SQL 2005. Like you said - it's a new possibility for writing sub-queries that you don't want to have to repeat again and again in a statement.
Among other things - it's a fancy way to write a derived table.
Common table Expression explained here:
http://msdn2.microsoft.com/en-us/library/ms190766.aspx
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 11, 2008 at 10:35 am
Ok...still struggling on this one....
I follow the example you gave, but I should have provided more relevant test data in my original post. I can't seem to draw the same result using the data that needs to be used. I started by creating a new table in both oracle and sql with the exact same data using the following script.
--ORACLE 10g
create table LEAD_TEST (ID_PRVD_ORG numeric, ID_PRSN numeric, DT_BGN date);
insert into LEAD_TEST values (20167, 20862, '20-JUL-01');
insert into LEAD_TEST values (20167, 20997, '20-JUL-01');
insert into LEAD_TEST values (20167, 9221101, '20-JUL-01');
insert into LEAD_TEST values (20169, 20842, '20-JUL-01');
insert into LEAD_TEST values (20169, 20862, '20-JUL-01');
insert into LEAD_TEST values (20169, 20912, '20-JUL-01');
insert into LEAD_TEST values (20169, 20912, '10-AUG-04');
insert into LEAD_TEST values (20170, 20828, '20-JUL-01');
insert into LEAD_TEST values (20170, 20848, '20-JUL-01');
insert into LEAD_TEST values (20170, 700010, '20-JUL-01');
insert into LEAD_TEST values (20174, 20858, '20-JUL-01');
insert into LEAD_TEST values (20174, 20873, '20-JUL-01');
insert into LEAD_TEST values (20174, 9222020, '20-JUL-01');
insert into LEAD_TEST values (20174, 9222048, '20-JUL-01');
insert into LEAD_TEST values (20174, 922066, '20-JUL-01');
--SQL
create table #LEAD_TEST (ID_PRVD_ORG numeric, ID_PRSN numeric, DT_BGN datetime)
insert into LEAD_TEST values (20167, 20862, '2001-07-20')
insert into LEAD_TEST values (20167, 20997, '2001-07-20')
insert into LEAD_TEST values (20167, 9221101, '2001-07-20')
insert into LEAD_TEST values (20169, 20842, '2001-07-20')
insert into LEAD_TEST values (20169, 20862, '2001-07-20')
insert into LEAD_TEST values (20169, 20912, '2001-07-20')
insert into LEAD_TEST values (20169, 20912, '2004-08-10')
insert into LEAD_TEST values (20170, 20828, '2001-07-20')
insert into LEAD_TEST values (20170, 20848, '2001-07-20')
insert into LEAD_TEST values (20170, 700010, '2001-07-20')
insert into LEAD_TEST values (20174, 20858, '2001-07-20')
insert into LEAD_TEST values (20174, 20873, '2001-07-20')
insert into LEAD_TEST values (20174, 9222020, '2001-07-20')
insert into LEAD_TEST values (20174, 9222048, '2001-07-20')
insert into LEAD_TEST values (20174, 922066, '2001-07-20')
You now have the same data in both Oracle and SQL.
What I'm trying to achive here is the same result in SQL as I'm getting in Oracle. The LEAD function is used in Oracle, but no equivalent exists in SQL. The Oracle query using the LEAD function is:
select DT_BGN, ID_PRSN, E.ID_PRVD_ORG, LEAD(E.ID_PRVD_ORG,1,NULL) OVER (PARTITION BY E.ID_PRSN ORDER BY E.ID_PRSN,E.DT_BGN ) AS LEAD_ID_PRVD_ORG FROM LEAD_TEST E;
My objective here is to produce the same result set in SQL and I'm on day #8 of banging my head against this, still with no solution.
All of the research I've done indicates that I'll have to do a self-join in SQL to the LEAD_TEST data. I've done every type of join possible in SQL and still can't produce the same results.
Any and all help would be greatly appreciated.
Thanks in advance,
Dave
April 11, 2008 at 12:44 pm
you'll need to post the expected results so i can double check my work.
April 11, 2008 at 6:53 pm
dmizelle (4/7/2008)
All of it looks good, but I'm lost within the following code, specifically the line starting with "with leader...". I've been working w/SQL for 12+ years and I've never seen this syntax.
It's the same as "SubQuery Refactoring" in Oracle. As some have already said, it's like a "Derived Table" (query used as a table in the FROM clause). In SQL Server, they are called CTE's or "Common Table Expressions".
Your turn... I might be able to help if you told me what the Oracle LEAD function actually does.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 11, 2008 at 8:51 pm
Without expected resultset..it is difficult to develop correct query but here is one guess..
with leadtest
as
(
select ID_PRVD_ORG , ID_PRSN , DT_BGN ,
row_number() over (partition by ID_PRSN order by ID_PRSN , DT_BGN) AS rownum
from #LEAD_TEST)
select l1.DT_BGN , l1.ID_PRSN , l1.ID_PRVD_ORG ,
l2.ID_PRVD_ORG AS NEXT_ID_PRVD_ORG
from leadtest l1 left outer join leadtest l2
on l2.rownum = l1.rownum + 1 AND
l2.ID_PRSN = l1.ID_PRSN
Please post expected resultset if this doesn't resolve your problem.
NJ
April 14, 2008 at 8:15 am
Sorry guys.... I should have posted the expected result set a long, long time ago. BEFORE I GO ANY FARTHER, THANK YOU THANK YOU THANK YOU FOR THE ANSWERS/REPLIES YOU'VE PROVIDED THUS FAR.
The ORACLE query I'm trying to convert has 3 calls to the LEAD function. To keep it as simple as possible, I'll include the 3 calls to the LEAD function as 3 separate select statements, all of which rely on the sample table & sample data created/loaded above. It's the 3rd column in each of the result sets that the lead function generates.
--QUERY 1:
select id_prvd_org, id_prsn, dt_bgn, LEAD(E.ID_PRVD_ORG,1,NULL) OVER (PARTITION BY E.ID_PRSN ORDER BY E.ID_PRSN,E.DT_BGN ) AS LEAD_ID_PRVD_ORG from lead_test e;
--RESULT SET 1:
ID_PRVD_ORG ID_PRSN DT_BGN LEAD_ID_PRVD_ORG
----------- ---------- --------- ----------------
20170 20828 20-JUL-01
20169 20842 20-JUL-01
20170 20848 20-JUL-01
20174 20858 20-JUL-01
20167 20862 20-JUL-01 20169
20169 20862 20-JUL-01
20174 20873 20-JUL-01
20169 20912 20-JUL-01 20169
20169 20912 10-AUG-04
20167 20997 20-JUL-01
20170 700010 20-JUL-01
ID_PRVD_ORG ID_PRSN DT_BGN LEAD_ID_PRVD_ORG
----------- ---------- --------- ----------------
20174 922066 20-JUL-01
20167 9221101 20-JUL-01
20174 9222020 20-JUL-01
20174 9222048 20-JUL-01
------------------------------------------------------------------------------------------
--QUERY 2:
SELECT ID_PRVD_ORG, ID_PRSN, DT_BGN, LEAD(E.DT_BGN,1,NULL) OVER (PARTITION BY E.ID_PRSN,E.ID_PRVD_ORG ORDER BY E.ID_PRSN,E.ID_PRVD_ORG,E.DT_BGN ) AS LEAD_DT_BGN_WITH_PRVD FROM LEAD_TEST E;
--RESULT SET 2:
ID_PRVD_ORG ID_PRSN DT_BGN LEAD_DT_B
----------- ---------- --------- ---------
20170 20828 20-JUL-01
20169 20842 20-JUL-01
20170 20848 20-JUL-01
20174 20858 20-JUL-01
20167 20862 20-JUL-01
20169 20862 20-JUL-01
20174 20873 20-JUL-01
20169 20912 20-JUL-01 10-AUG-04
20169 20912 10-AUG-04
20167 20997 20-JUL-01
20170 700010 20-JUL-01
ID_PRVD_ORG ID_PRSN DT_BGN LEAD_DT_B
----------- ---------- --------- ---------
20174 922066 20-JUL-01
20167 9221101 20-JUL-01
20174 9222020 20-JUL-01
20174 9222048 20-JUL-01
------------------------------------------------------------------------------------------
--QUERY 3:
SELECT ID_PRVD_ORG, ID_PRSN, DT_BGN, LEAD(E.DT_BGN,1,NULL) OVER (PARTITION BY E.ID_PRSN ORDER BY E.ID_PRSN,E.DT_BGN) AS LEAD_DT_BGN FROM LEAD_TEST E;
--RESULT SET 3:
ID_PRVD_ORG ID_PRSN DT_BGN LEAD_DT_B
----------- ---------- --------- ---------
20170 20828 20-JUL-01
20169 20842 20-JUL-01
20170 20848 20-JUL-01
20174 20858 20-JUL-01
20167 20862 20-JUL-01 20-JUL-01
20169 20862 20-JUL-01
20174 20873 20-JUL-01
20169 20912 20-JUL-01 10-AUG-04
20169 20912 10-AUG-04
20167 20997 20-JUL-01
20170 700010 20-JUL-01
ID_PRVD_ORG ID_PRSN DT_BGN LEAD_DT_B
----------- ---------- --------- ---------
20174 922066 20-JUL-01
20167 9221101 20-JUL-01
20174 9222020 20-JUL-01
20174 9222048 20-JUL-01
Thanks again,
Dave
April 14, 2008 at 10:12 am
here are your first two LEAD() queries translated. i'll leave the third up to you.:)
within the CTE, LEAD() is replaced by ROW_NUMBER(). the LEAD() function's PARTITION BY and ORDER BY remain the same. when self-joining the CTE, you'll JOIN on the ROW_NUMBER() column ("seq" in my sample) and the PARTITION BY columns.
create table #LEAD_TEST (ID_PRVD_ORG numeric, ID_PRSN numeric, DT_BGN datetime)
insert into #LEAD_TEST values (20167, 20862, '2001-07-20')
insert into #LEAD_TEST values (20167, 20997, '2001-07-20')
insert into #LEAD_TEST values (20167, 9221101, '2001-07-20')
insert into #LEAD_TEST values (20169, 20842, '2001-07-20')
insert into #LEAD_TEST values (20169, 20862, '2001-07-20')
insert into #LEAD_TEST values (20169, 20912, '2001-07-20')
insert into #LEAD_TEST values (20169, 20912, '2004-08-10')
insert into #LEAD_TEST values (20170, 20828, '2001-07-20')
insert into #LEAD_TEST values (20170, 20848, '2001-07-20')
insert into #LEAD_TEST values (20170, 700010, '2001-07-20')
insert into #LEAD_TEST values (20174, 20858, '2001-07-20')
insert into #LEAD_TEST values (20174, 20873, '2001-07-20')
insert into #LEAD_TEST values (20174, 9222020, '2001-07-20')
insert into #LEAD_TEST values (20174, 9222048, '2001-07-20')
insert into #LEAD_TEST values (20174, 922066, '2001-07-20')
;with leader( ID_PRVD_ORG, ID_PRSN, DT_BGN, seq )
as
( select ID_PRVD_ORG, ID_PRSN, DT_BGN,
row_number() over (PARTITION BY ID_PRSN
ORDER BY ID_PRSN,DT_BGN ) as seq
from #LEAD_TEST
)
select L.ID_PRVD_ORG, L.ID_PRSN, L.DT_BGN, L.ID_PRSN, L.ID_PRVD_ORG,
N.ID_PRVD_ORG as LEAD_ID_PRVD_ORG
rom leader as L left outer join leader as N
on L.seq + 1 = N.seq and L.ID_PRSN = N.ID_PRSN
;with leader( ID_PRVD_ORG, ID_PRSN, DT_BGN, seq )
as
( select ID_PRVD_ORG, ID_PRSN, DT_BGN,
row_number() over (PARTITION BY E.ID_PRSN,E.ID_PRVD_ORG
ORDER BY E.ID_PRSN,E.ID_PRVD_ORG,E.DT_BGN ) as seq
from #LEAD_TEST as E
)
select L.ID_PRVD_ORG, L.ID_PRSN, L.DT_BGN,
N.DT_BGN as LEAD_ID_PRVD_ORG
from leader as L left outer join leader as N
on L.seq + 1 = N.seq
and L.ID_PRSN = N.ID_PRSN
and L.ID_PRVD_ORG = N.ID_PRVD_ORG
April 14, 2008 at 10:28 am
David.Mizelle (4/14/2008)
Sorry guys.... I should have posted the expected result set a long, long time ago. BEFORE I GO ANY FARTHER, THANK YOU THANK YOU THANK YOU FOR THE ANSWERS/REPLIES YOU'VE PROVIDED THUS FAR.
I'd still like to know what the LEAD function does, if you don't mind.
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2008 at 10:32 am
LEAD()/LAG() give access to next/prior rows in an ordered set without needing a self join.
http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php
April 14, 2008 at 10:44 am
antonio.collins (4/14/2008)
http://www.oracle-base.com/articles/misc/LagLeadAnalyticFunctions.php%5B/quote%5D
Thanks, Antonio.
Heh... "Ordered Sets" in an RDBMS... I use them a fair bit but what a PITA... 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
April 14, 2008 at 11:56 am
i agree. there seems to be more and more demand for ordered processing, but i'm sure codd and date would say that order should be irrelevant from a relational data standpoint. stuff like this *should* be handled by the data requester (app/presentation layer/report/etc). still, ya gotta do what ya gotta do.
April 14, 2008 at 12:00 pm
Guys,
Thanks again for the help. I finally have a workaround. Now I just have to figure out how to plug it back into the original query.
...PITA is an understatement!!
Thanks again,
Dave
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply