Archives of the TeradataForum

Message Posted: Fri, 28 May 2010 @ 11:37:55 GMT

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

Hi Dmitriy,

Thanks a ton for your quick response, you are absolutely correct the count is not zero and count is 25870, Basically we are working on migration project and here is the query from oracle where we have change in Teradata.

Please have a look and let me know your suggestion. Once again thanks you so much for your help

     CREATE MATERIALIZED VIEW
     "WOFDWINT"."V_INT_MKT_RETN_TENYEARS_FWD_MV"
       ORGANIZATION HEAP PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS NOLOGGING

       STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
       PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
       TABLESPACE "RPT_DATA_01"
       PARALLEL 6

       BUILD IMMEDIATE
       USING INDEX
       REFRESH FORCE ON DEMAND

       USING DEFAULT LOCAL ROLLBACK SEGMENT
       DISABLE QUERY REWRITE
       AS 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>>