![](//teradataforum.com/imgs/ptdfw1.gif) |
![](//teradataforum.com/imgs/ptdfw2.gif) |
Archives of the TeradataForum
Message Posted: Mon, 31 May 2010 @ 09:58:37 GMT
Subj: | | Re: ROW_NUMBER()OVER |
|
From: | | Ahmad, Syed Rehan |
Thanks a lot Dieter for your help. However this query and the oracle query does not match in counts. I checked and found that table in both
environment have same number of records. So it should also match
For you reference again I am pasting the Oracle query
SELECT t1.period, t1.cust_id, t1.veh_mdl_cd, t1.veh_mk_nm, t1.veh_sres_nm,
t1.vin_nb, t1.cust_actn_dt, t1.new_vin_nb,
t1.new_cust_act_dt,
t1.new_veh_mdl_cd, t1.new_veh_mk_nm,
t1.new_veh_sres_nm,
1 tenyears_unit,
ROUND (MONTHS_BETWEEN (t1.new_cust_act_dt,
t1.cust_actn_dt))
nbr_months
FROM (SELECT period, cust_id, veh_mdl_cd, veh_mk_nm, veh_sres_nm, vin_nb,
cust_actn_dt, new_vin_nb, new_cust_act_dt, new_veh_mdl_cd,
new_veh_mk_nm, new_veh_sres_nm, ROWNUM AS all_rownum
FROM (SELECT TO_CHAR (a.cust_actn_dt, 'YYYY') AS period,
a.cust_id, a.veh_mdl_cd, a.veh_mk_nm, a.veh_sres_nm,
a.vin_nb, a.cust_actn_dt, b.vin_nb AS new_vin_nb,
b.cust_actn_dt AS new_cust_act_dt,
b.veh_mdl_cd AS new_veh_mdl_cd,
b.veh_mk_nm AS new_veh_mk_nm,
b.veh_sres_nm AS new_veh_sres_nm
FROM wofdwint.int_set_cust_retn a,
wofdwint.int_set_cust_retn b
WHERE a.vin_nb <> b.vin_nb
AND a.cust_id = b.cust_id
AND b.cust_actn_dt BETWEEN a.cust_actn_dt
AND ADD_MONTHS
(a.cust_actn_dt,120)
ORDER BY a.cust_id, a.cust_actn_dt ASC, b.cust_actn_dt ASC)) t1,
(SELECT period, cust_id, vin_nb, MIN (ROWNUM) AS min_rownum
FROM (SELECT TO_CHAR (a.cust_actn_dt, 'YYYY') AS period,
a.cust_id, a.veh_mk_nm, a.veh_sres_nm,
a.vin_nb,
a.cust_actn_dt, b.vin_nb AS new_vin_nb,
b.cust_actn_dt AS new_cust_act_dt,
b.veh_mk_nm AS new_veh_mk_nm,
b.veh_sres_nm AS new_veh_sres_nm
FROM wofdwint.int_set_cust_retn a,
wofdwint.int_set_cust_retn b
WHERE a.vin_nb <> b.vin_nb
AND a.cust_id = b.cust_id
AND b.cust_actn_dt BETWEEN
a.cust_actn_dt AND ADD_MONTHS (a.cust_actn_dt, +120)
ORDER BY a.cust_id, a.cust_actn_dt ASC, b.cust_actn_dt ASC)
GROUP BY period, cust_id, vin_nb) t2
WHERE t1.period = t2.period
AND t1.cust_id = t2.cust_id
AND t1.vin_nb = t2.vin_nb
AND t1.all_rownum = t2.min_rownum
| |