Archives of the TeradataForum

Message Posted: Mon, 31 May 2010 @ 11:52:57 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Syed,

try the Oracle query using row_number instead of rownum and see if it returns the same rows:

     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,
                     ROW_NUMBER() OVER (ORDER BY a.cust_id, a.cust_actn_dt
     ASC, b.cust_actn_dt ASC) 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)
                      )
     t1,



             (SELECT   period, cust_id, vin_nb, MIN (rn) 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,
                                 ROW_NUMBER() OVER (ORDER BY a.cust_id,
     a.cust_actn_dt ASC, b.cust_actn_dt) AS rn
                            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)

     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

	<Prev	Next>		<<First	<Prev	Next>	Last>>