Archives of the TeradataForum

Message Posted: Mon, 31 May 2010 @ 09:58:37 GMT

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

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

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