April 14, 2008 at 12:23 pm
antonio.collins (4/14/2008)
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.
Interestingly enough - everyone keeps ignoing the fact that there are lots and lots of kinds of sets defined in mathematics. Among which - ordered sets. Can you do a lot with simple sets (those defined with no order)? Absolutely, and they scale very well. The issue comes in when you have to start playing all of the reindeer games when the problem you need to solve intrinsically calls for an order.
----------------------------------------------------------------------------------
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 14, 2008 at 2:49 pm
Well, just when I thought the nightmare was over I discovered that it isn't....
At this point we have the following work arounds. We have one for each of the 3 calls to Oracle's LEAD function. They are:
-- 1)
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 episode )
select N.ID_PRVD_ORG 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
-- 2)
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, ID_PRVD_ORG ORDER BY ID_PRSN, ID_PRVD_ORG, DT_BGN ) as seq from episode )
select N.DT_BGN as LEAD_DT_BGN_WITH_PRVD 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
-- 3)
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 episode )
select N.DT_BGN as LEAD_DT_BGN from leader as L left outer join leader as N on L.seq + 1 = N.seq and L.ID_PRSN = N.ID_PRSN
At this point I'm trying to plug these 3 calls back into the original query that birthed this nightmare. Even though each of the 3 queries returns the same result set that each SINGLE call the the cooresponding LEAD function produces, when combined in the same query, the result set is far from Oracle's result set. I can't seem to make it work. Imagine that...
If I run each of the queries separately, I get 254, 120, & 254 rows returned, respectively. When I try to combine them, I'm not getting null values for the columns that should have null values. It's either returning a complete row with values or a complete row w/nulls. In Oracle, the result set is a mix of values and nulls, which is what I'm trying to reproduce.
Given that I'm on day #9 with this nightmare, I'm willing to do WHATEVER it takes to take my technical challenges into some other realm, which will hopefully be galaxies away from any SQL Server database conversion that requires conversion of Oracle's LEAD function. Even though it wouldn't be the prettiest solution, I'm considering writing a totally one-time usable function that includes all pieces of the where clause that this stupid function requires. I simply have no other ideas at this point and I'm real, real tired of dealing with this issue.
Any and all help would be greatly appreciated.
Thanks again for all of the help.
Dave
April 14, 2008 at 3:09 pm
i don't know if CTEs can be nested, but if not you can do everything in 2 queries. your first and third datesets calculate lead over (ID_PRSN and DT_BGN) so the following pseudo-code should get you started. (sorry, but i can't give you tested code at this time.)
select ID_PRVD_ORG, ID_PRSN, DT_BGN,
ROW_NUMBER() over (PARTITION BY ID_PRSN
ORDER BY ID_PRSN,DT_BGN ) as seq_A,
ROW_NUMBER() over (PARTITION BY ID_PRSN, ID_PRVD_ORG
ORDER BY ID_PRSN, ID_PRVD_ORG, DT_BGN ) as seq_B
from {your_table}
into #X
select X.{cols},
A.ID_PRVD_ORG as LEAD_ID_PRVD_ORG ,
B.DT_BGN as LEAD_DT_BGN_WITH_PRVD ,
A.DT_BGN as LEAD_DT_BGN
from #X as X left outer join #X as A on {seq_A, ID_PRSN}
left outer join #X as B on {seq_B, ID_PRSN, ID_PRVD_ORG}
May 16, 2008 at 10:23 pm
You can do all in one query; you just need to add an extra row_number column with the extra partition by criteria and then add an extra self join. This query should give what you need :
;with leader( ID_PRVD_ORG, ID_PRSN, DT_BGN, seq, SEQ2 )
as
( select ID_PRVD_ORG, ID_PRSN, DT_BGN,
row_number() over (PARTITION BY ID_PRSN
ORDER BY ID_PRSN,DT_BGN ) as seq,
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 seq2
from LEAD_TEST AS e
)
select L.ID_PRSN, L.ID_PRVD_ORG, L.DT_BGN,
N.ID_PRVD_ORG as LEAD_ID_PRVD_ORG_seq , N.DT_BGN as LEAD_DT_BGN_PRVD_ORG_seq ,
N1.DT_BGN as LEAD_DT_BGN_PRVD_seq2
from leader as L left outer join leader as N
on L.seq + 1 = N.seq and L.ID_PRSN = N.ID_PRSN
left outer join leader as N1
on L.seq2 + 1 = N1.seq2 and L.ID_PRSN = N1.ID_PRSN
and L.ID_PRVD_ORG = N1.ID_PRVD_ORG
order by L.ID_PRSN, L.ID_PRVD_ORG
Good luck.
Rafael Salas
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply